How to insert items in column A into a range of cells and copying the range

**arbazhamayun** · 02-06-2018, 05:09 AM

Hi guys,

I have this relatively simple problem, but I have yet to find a solution to this problem.

What I want to do is this:

I have a range of strings in column A for simplicity lets say like this:

Udklip.JPG

I create a combined string based of different cells where one one of the cell in Column D references the value from column A - see here:

Udklip2.JPG

Udklip3.JPG

Now I want to copy the following and insert right below the grey area:

Udklip4.JPG

But as you can see it does not reference the next item in column A, which is "Oranges" but rather it jumps 3 indeces, since this is how much A1 is moved.

How can I achieve this?

**Glenn Kennedy** · 02-06-2018, 05:20 AM

What do you want in the 2nd and 3rd rows? Do you always it to increment by one row every 4th row?

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**leelnich** · 02-06-2018, 05:31 AM

Paste this in D2, copy D2:D5 (3 rows), then select destination cells (multiples of 3):

Please Login or Register  to view this content.

References:
$A$1:$A$4 = lookup_range
ROW(D$2) = first cell of copied section
/3 = number of rows copied

**arbazhamayun** · 02-06-2018, 05:38 AM

I want my list of values in column A to vary i.e. if I have 100 values or 4 values it should not matter for my copying, it should always jump one down!

I have attached my actual project.

So the following shows what I want:

Udklip.JPG

But this is what actually happens when I copy it:

Udklip2.JPG

**leelnich** · 02-06-2018, 05:48 AM

Paste this in J6 and copy down. Number will change each time first line repeats:

Please Login or Register  to view this content.

...and this in L6 (copied sectionally):

Please Login or Register  to view this content.

**Glenn Kennedy** · 02-06-2018, 05:48 AM

In L6:

=OFFSET($B$6,INT((ROWS(L$6:L6)-1)/26),,,)

in L25:

=OFFSET($B$6,INT((ROWS(L$25:L25)-1)/26),,,)

and copy down as previously. being in DK, you will probably need ; and not , in the formulae

**leelnich** · 02-06-2018, 06:51 AM

Nice one Glenn. Could you simplify it?

Please Login or Register  to view this content.

...since Excel automatically truncates fractions in range references.
Also, no need to change L25:

Please Login or Register  to view this content.

...works.

**arbazhamayun** · 02-06-2018, 08:51 AM

This worked for me. I made a column next to my range where I gave each instance a number 1..2..3..4 etc. Then I changed the /3 part with /max(range) and this did the trick for me.

**arbazhamayun** · 02-06-2018, 08:52 AM

Originally Posted by leelnich

Paste this in D2, copy D2:D5 (3 rows), then select destination cells (multiples of 3):

Please Login or Register  to view this content.

References:
$A$1:$A$4 = lookup_range
ROW(D$2) = first cell of copied section
/3 = number of rows copied

This worked for me. I made a column next to my range where I gave each instance a number 1..2..3..4 etc. Then I changed the /3 part with /max(range) and this did the trick for me.

**leelnich** · 02-06-2018, 09:00 AM

Excellent, glad we could help! If that concludes your thread, please mark it as SOLVED (Thread Tools, up top). And since you're new...
Clicking the Add Reputation star below helpful posts is a nice way to show appreciation to those who contributed. Regards - Lee

**arbazhamayun** · 02-06-2018, 09:01 AM

Originally Posted by leelnich

Excellent, glad we could help! If that concludes your thread, please mark it as SOLVED (Thread Tools, up top). And since you're new...
Clicking the Add Reputation star below helpful posts is a nice way to show appreciation to those who contributed. Regards - Lee

Done and done.

Thanks for the quick responses. Really appreciate it!

**arbazhamayun** · 02-06-2018, 11:55 AM

Well now I tried a similar setup but it is not working.

I have attached a sample file.

It seems now I get a duplicate each time i copy down I dont understand why.

problem.JPG

**Glenn Kennedy** · 02-06-2018, 12:05 PM

Shouldn't it be:

=INDEX($B$5:$B$13,1+(ROW()-ROW(L$5))/4)

personal preference.... I prefer this:

=INDEX($B$5:$B$13,1+INT((ROWS($1:1)-1)/4))

**arbazhamayun** · 02-06-2018, 12:14 PM

Originally Posted by Glenn Kennedy

Shouldn't it be:

=INDEX($B$5:$B$13,1+(ROW()-ROW(L$5))/4)

personal preference.... I prefer this:

=INDEX($B$5:$B$13,1+INT((ROWS($1:1)-1)/4))

Ah yes thank you. I had not fully understood the formula from the beginning.

**leelnich** · 02-06-2018, 03:44 PM

Originally Posted by Glenn Kennedy

Shouldn't it be:

=INDEX($B$5:$B$13,1+(ROW()-ROW(L$5))/4)

personal preference.... I prefer this:

=INDEX($B$5:$B$13,1+INT((ROWS($1:1)-1)/4))

Glenn, do you mean ROWS($5:5)? $1:1 happened to align with the modulus in this case, but introduces an error if the first copied cell is eg. L7.
That being said, your suggested syntax is perhaps a clearer expression of intent than mine, and uses 1 function call instead of 2.

03:16 Hang on, having a re-think... $1:1 does NOT error with L7 - but I don't know why...
03:20 Got it- the relative offset is copied with the formula. My bad, I missed that.

I would say this though, referencing rows outside of the relevant data area is risky. The formula may break if an "extraneous" row is deleted.
Please see the attached file for a demonstration...

How to insert items in column A into a range of cells and copying the range

LinkBack

Thread Tools

Rate This Thread

Display

How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Re: How to insert items in column A into a range of cells and copying the range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Adding Items to List box from cell range but skipping items duplicated in another range

[SOLVED] Copying cell C1 and pasting to range of cells in column C

Variable range SUM , base range on other column blank cells?

Excel 2007 : Range Contains Text - Then Insert Range from Other Cells

Pulling Items from a multiple column Range

[SOLVED] macro for copying a range to a column of cells

[SOLVED] How to count number of items(cells) in range?

Bookmarks

Bookmarks

Posting Permissions