# Copy by pattern?

1. ## Copy by pattern?

Hi, I need help to fill a column based with data from other cells in the sheet. The problem is that the data is in different columns.

For example, If I have data in following four columns (12 cells), I need that data transposed to a single column.

B1 C1 D1 E1
B2 C2 D2 E2
B3 C3 D3 E3

column A needs to be filled in this order with the data from the following cells;

B1
C1
D1
E1
B2
C2
D2
E2
B3
C3
D3
E3

I need something I can adapt for different tasks, some data tables have 4 columns, some have 10.
I can't copy a normal formula as excel doesn't copy the pattern when I fill down. Some of my data tables have hundreds of rows so I can't do manually.

Any help would be greatly appreciated.

Brian

2. ## Re: Copy by pattern?

In cell A1 of Sheet1 where you want your data transposed into:

``Please Login or Register  to view this content.``
Sheet2!\$A\$1 is the upper left hand corner of the data square that you are starting with. (I assumed it was starting right in cell A1 of Sheet2, it might be somewhere else, give it the appropriate reference.)

1 is the position of the row that you are starting the formula in, so it's "1" for "A1" in this example formula.

4 is how many columns wide your data is; you will need to either change this manually, or possibly run this off another cell as a reference (and that cell could have, for example, the function =COUNTA(Sheet2!A1:AA1) in it or something).

You will need to pull this down the same number of cells as there are cells in the target range. I would probably wrap an IF(check, do, "") or something.

3. ## Re: Copy by pattern?

If you want to avoid a volatile function, then you can use this in A1:

=INDEX(\$B\$1:\$E\$3,INT((ROWS(\$1:1)-1)/4)+1,MOD(ROWS(\$1:1)-1,4)+1)

where the data is assumed to be in B1 to E3 of the same sheet. Change the items in red to accommodate more columns of data.

Hope this helps.

Pete

4. ## Re: Copy by pattern?

I need something I can adapt for different tasks, some data tables have 4 columns, some have 10.
I can't copy a normal formula as excel doesn't copy the pattern when I fill down. Some of my data tables have hundreds of rows so I can't do manually.
You could make a dynamic named range formula in Name Manager. These automatically size to fit the data.

Then use a helper cell ... say B1 with this formula
Formula:
`Please Login or Register  to view this content.`

Then you can replace the range and 4s in Pete's formula

=INDEX(\$B\$1:\$E\$3,INT((ROWS(\$1:1)-1)/4)+1,MOD(ROWS(\$1:1)-1,4)+1)

with

=INDEX(Named_Range,INT((ROWS(\$1:1)-1)/\$B\$1)+1,MOD(ROWS(\$1:1)-1,\$B\$1)+1)

5. ## Re: Copy by pattern?

Ben, when I modified your formula for my data I broke it -sorry. I will have a play with it and see if I can understand what it is doing and hopefully learn a thing or two.
Pete, your formula worked straight out of the box and I can mark this as solved. You have saved me hours of (unpaid) work.

A big thank you to you both.

6. ## Re: Copy by pattern?

Thanks FlameRetired. I will use that tweak for sure.

7. ## Re: Copy by pattern?

You're welcome. Thanks for the feedback and please do mark as SOLVED.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1