Transposing single column data to multiple rows

Hi,

This is my first post. Hope someone could help me with this.

I have one column data that goes in 5 groups:

A0
A1
A2
A3
A4

B0
B1
B2
B3
B4

C0
C1
C2
C3
C4

.
.
.
.
.

So on. [Note that there is blank between the data groups.]

I want all data transposed as such:

A0 A1 A2 A3 A4
B0 B1 B2 B3 B4
C0 C1 C2 C3 C4
.....
.....

How do I do that?

Thank you so much.

Audrey

Originally Posted by Special-K

This works

copy this formula into Sheet2 A1 and across to E1
then copy Sheet 2 A1:E1 downwards

=INDIRECT("Sheet1!a"&INT((ROW()-1)/6)+(ROW()-1)*6+COLUMN())

When the zeroes appear the data ends in your original sheet

Thank you for your help. I tried your formula; unfortunately it works correctly for first 5 groups of data. Starting from the 6th group it messes up by placing the first data in that group to the E7 with the value of 0. Starting from 12th group it places second data to the first row, and second data to the 3rd row with the value of 0.

Can you fix your formula please?

Originally Posted by Haseeb A

Assume the range starts on A2 to down, so try this in B2, then copy down;

=INDEX($A$2:$A$1000,COLUMNS($B2:B2)+(ROWS(B$2:B2)*6-5)-1)

Thanks a lot Haseeb. This worked just like I wanted.