Transpose every set of 5 rows into 1 row

[howdedo]

I have a large data set where a single account is represented as a fixed number of rows (i.e. 5) in a single column. In other words, the first 5 rows in the column represent a single account. The next 5 rows represent the next account, etc.

I want to convert each set of 5 rows into a single row with 5 colums so that I can sort and aggregate the data. How can I convert every 5 rows into 5 columns in a single row?

For example, I want to convert this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

into this:
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15

Is this possible to do this in a manner that could be easily be repeated to accomodate 10-15 thousand rows of input data in this format?

Thanks for your help.
Tom

 

[Domenic]

Assuming that A2:A16 contains the data, try...

C2, copied across and down:

=INDEX($A$2:$A$16,ROWS(C$2:C2)*5-5+COLUMNS($C2:C2))

Adjust the references, accordingly.

 

[howdedo]

Works great Domenic! Thanks so much for your help!

 

[ccarlos]

Works great Domenic! Thanks so much for your help!

Thank you for this. It has been more than 5 years but the posts are still useful!

 

[CarlPH]

Thanks Domenic!, amazing how can this be achieved with just standard formulas

 

[Domenic]

Thanks Domenic!, amazing how can this be achieved with just standard formulas

Actually, the formula uses standard/native functions that are available to all versions of Excel...

 

[memyself]

Actually, the formula uses standard/native functions that are available to all versions of Excel...

This is my exact question (except I want to transpose every 6 lines), but I can't get it to work... What do you mean by "C2, copied across and down:" I don't know what I'm doing wrong.

 

[Domenic]

This is my exact question (except I want to transpose every 6 lines), but I can't get it to work... What do you mean by "C2, copied across and down:" I don't know what I'm doing wrong.

First, enter the following formula in cell C2...

=INDEX($A$2:$A$16,ROWS(C$2:C2)*6-6+COLUMNS($C2:C2))

Then, place your cursor over the lower right corner of C2 until it changes to a cross, click and drag across to H2. Then, place your cursor over the lower right corner of H2 until it changes to a cross, click and drag down.

Hope this helps!

 

[memyself]

Thank you for breaking that down for me. I have it working now.

 

[asalai]

Thank you for breaking that down for me. I have it working now.