Transpose groups of data from column to row

alexgoodwill

New Member
Joined
Dec 11, 2011
Messages
23
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!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

GreasySpot

New Member
Joined
Feb 1, 2013
Messages
48
Are all 100 groups in 3's?
When you put them in rows are the in the same cell or 3 cells?
 

alexgoodwill

New Member
Joined
Dec 11, 2011
Messages
23
Yes the 100 groups are in 3's in a single column.

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

Cheers!
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><tbody>
</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.
 

alexgoodwill

New Member
Joined
Dec 11, 2011
Messages
23

ADVERTISEMENT

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

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top