Transpose groups of data from column to row

[alexgoodwill]

Hi,

I have 100 groups of data in a single column. I will list the below example using 3 groups.

A1
B1
C1

A2
B2
C2

A3
B3
C3

I would like to transpose them into a single row for each group, the output will be :
A1 B1 C1
A2 B2 C2
A3 B3 C3


I can use copy as transpose when I do each group individually but it will take a long time. Is there any way to do it with a formula?


Cheers!

 

[GreasySpot]

Are all 100 groups in 3's?
When you put them in rows are the in the same cell or 3 cells?

 

[alexgoodwill]

Yes the 100 groups are in 3's in a single column.

When transpose to rows, should be in 3 different cells.

Cheers!

 

[Comfy]

This method depends on how your data is laid out.

I've assumed that your data starts in A1 and each group is separated by one row:

Excel 2010

	A	B	C	D	E
A1	A1	B1	C1
B1	A2	B2	C2
C1	A3	B3	C3
A2
B2
C2
A3
B3
C3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=INDEX($A:$A,COLUMN()-2+(ROW()-1)*4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

For a more specific answer please state which cell your data starts in and also the first cell that you wish the data to appear.

 

[alexgoodwill]

You are right, that is how my data is being laid out!

 

[Comfy]

You are right, that is how my data is being laid out!

Ok, so if you place the formula in C1 and copy it to E1 and then down as many rows as you need you should get the outcome you require.

It will need adjusting if the columns are not C:E

 

[alexgoodwill]

Yes Comfy, I managed to do it. Thanks a Million!