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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,386
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,386
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,881
Messages
5,834,226
Members
430,267
Latest member
Excel_User_4

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