Hello

I have a large spreadsheet of excel data formatted in colums when I need it
in rows.

Currently it is e.g.,

A K 1
A l 2
A m 3
A n 4
B k 1
B l 2
B m 3
B n 4

When we need it to look like e.g.,:
k l m n
A 1 2 3 4
B 1 2 3 4

I have figured out that I can cut and paste/transpose but please help me
keep my hair...is there a faster way?

Thanks for your help
Maria in New Zealand

One approach - you could create a helper column for your data by
concatenating the first two columns:

A B C D
1 AK A K 1
2 Al A l 2
3 Am A m 3
4 An A n 4
5 Bk B k 1
6 Bl B l 2
7 Bm B m 3
8 Bn B n 4

A1: =B1&C1



Then you could use Vlookup to get your data into the format you want:
F G H I J
1 k l m n
2 A
3 B

G2: =VLOOKUP($F2&G$1, $A$1:$D$8, 4, 0)
copy across and down.


"Hair loss by excel" wrote:


Hello

I have a large spreadsheet of excel data formatted in colums when I need it
in rows.

Currently it is e.g.,

A K 1
A l 2
A m 3
A n 4
B k 1
B l 2
B m 3
B n 4

When we need it to look like e.g.,:
k l m n
A 1 2 3 4
B 1 2 3 4

I have figured out that I can cut and paste/transpose but please help me
keep my hair...is there a faster way?

Thanks for your help
Maria in New Zealand