How to transpose all columns to rows?

[diabolic]

Hi,

For example, I have data like below:

F1	F2	F3	F4	F5	F6	F7	F8
AAA0001	AAA0002	AAA0003	AAA0004	AAA0005	AAA0006	AAA0007	AAA0008
973,880.89	4,782,158.16	121,393.21	282,760.28	298,333.72	89,445.06	106,041.15	104,381.48

The number of columns is not fixed (could be more than 1K).

I want to change my data to below:

F1	AAA0001	973,880.89
F2	AAA0002	4,782,158.16
F3	AAA0003	121,393.21
F4	AAA0004	282,760.28
F5	AAA0005	298,333.72
F6	AAA0006	89,445.06
F7	AAA0007	106,041.15
F8	AAA0008	104,381.48

I have tried to use "Transpose", but I do not get what I want.

Any suggestion? Thanks.

[BenMoss · Accepted solution]

A couple of ways to do this.

Use the transpose tool and have everything else as a data field..

Then use a summarize tool. Grouping by the name field and then concatenating the value field..

Finally use the text to columns on the concatenated field to split it out to different columns.

Ben

[BenMoss]

Just to add to this, having though about it for a bit longer, would be the following:

Record ID your data. When doing the transpose use the recordID as a key field and then transpose everything else.

Then use the cross-tab tool. Your name field will be your 'group by', your RecordID field will be the header field, and then the value field will be your 'value', as you will only have one record in each at the bottom of the cross-tab tool select 'concatenate'.

This will be dynamic to give you three columns if you had three rows. Whereas the first solution you would have to change the number of columns to split to.

Ben

[Kaviyarasan_P]

Answer:

[razzy]

This is what i have i am trying to use your idea but it deos not seem working for me. I have this data set:

Col A     Col B     Col C

Light on AB WT 0
Light on AB WT 0
Light on AB WT 3072
Light on AB WT 704
Light on AB WT 626
Light on AB WT 634
Light on AB WT 1924
Light on AB WT 2472
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on AB 272 25 µM 0
Light on AB 272 25 µM 0
Light on AB 272 25 µM 1376
Light on AB 272 25 µM 0
Light on AB 272 25 µM 814
Light on AB 272 25 µM 0
Light on AB 272 25 µM 4
Light on AB 272 25 µM 0
Light on ndufs2 272 25 µM 0
Light on ndufs2 272 25 µM 0
Light on ndufs2 272 25 µM 60
Light on ndufs2 272 25 µM 1594
Light on ndufs2 272 25 µM 146
Light on ndufs2 272 25 µM 2104
Light on ndufs2 272 25 µM 0
Light on ndufs2 272 25 µM 0
Light on AB 259 50 µM 0
Light on AB 259 50 µM 276
Light on AB 259 50 µM 126
Light on AB 259 50 µM 222
Light on AB 259 50 µM 0
Light on AB 259 50 µM 0
Light on AB 259 50 µM 70
Light on AB 259 50 µM 4522

 what i want to do is to transpose and get a matrix like this:

1.	0.	0.	3072.	704.	626.	634.	1924.	2472.
2.	0.	0.	3406.	2036.	398.	1222.	2628.	0.
3.	20.	8.	2744.	1926.	306.	126.	2492.	0.
4.	0.	10.	2490.	2798.	0.	2268.	2442.	40.

How do i solve this thanks