# Transpose groups of data from column to row

#### alexgoodwill

##### New Member
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!

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Are all 100 groups in 3's?
When you put them in rows are the in the same cell or 3 cells?

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

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

Cheers!

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
ABCDE
1A1A1B1C1
2B1A2B2C2
3C1A3B3C3
4000
5A2000
6B2000
7C2000
8000
9A3000
10B3000
11C3000
12000
13000
14000
15000

</tbody>
Sheet1

Worksheet Formulas
CellFormula
C1=INDEX(\$A:\$A,COLUMN()-2+(ROW()-1)*4)

</tbody>

<tbody>
</tbody>

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.

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

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

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

Replies
8
Views
154
Replies
4
Views
175
Replies
2
Views
75
Replies
0
Views
105
Replies
6
Views
210

1,203,488
Messages
6,055,716
Members
444,811
Latest member
NotJack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back