Splitting data in a column

[Barb Reinhardt]

Is there a way to split data that's in columns similar to that shown below

A Data for A
B Data for B
C Data for C
D Data for D
E Data for E
A Data for A2
B Data for B2
C Data for C2
D Data for D2
E Data for E2

so that it's in the format

A B C D
E
Data for A Data for B Data for C Data for D Data for E
Data for A2 Data for B2 Data for C2 Data for D2 Data for E2

I've done this quickly and easily with SAS JMP, but no longer have that
available.

Thanks in advance,

Barb Reinhardt

 

[Domenic]

Assuming that Column A contains your data, and the format for your data
is consistent, try the following...

B1, copied across and down:

=OFFSET($A$1,((ROW(1:1)-ROW($B$1)+1)*5-5)+(COLUMN(B:B)-COLUMN($B:$B)),0)

Then, if you wish, you can select your new columns, Copy > Paste Special

Values, and delete the old column.

Hope this helps!

 

[Guest]

The TRANSPOSE function converts a vertical range to horizontal, or
horizontal to vertical.
It's normally used as an Array function.
With an array function, you need to press Shift+Ctrl+Enter.

With just your simple example of a single column of 10 rows starting in A1:
- Select B1:F1
- Type this formula:
=TRANSPOSE(A1:A5)
- Press Shift+Ctrl+Enter
- Select B2:F2
- Use this formula (you can copy & modify):
=TRANSPOSE(A6:A10)
- Press Shift+Ctrl+Enter

With many rows in your single column:
- Select B1:F1
- Use this formula:
=TRANSPOSE(OFFSET($A$1,(ROWS(B$1:B1)-1)*5,0,1,1):OFFSET($A$1,((ROWS(B$1:B1)-
1)*5)+4,0,1,1))
- Press Shift+Ctrl+Enter
- Copy down to complete your matrix

Good luck.