Excel Transpose?

[truitt20]

I have data formatted in excel like the below:


a b c
xxx 10 20 60
yyy 30 10 20
ttt 40 5 15
vvv 50 70 30


And I would like to automatically get the data formatted in another worksheet like this via formula or some other auto means:


xxx 10 a
xxx 20 b
xxx 60 c
yyy 30 a
yyy 10 b
yyy 20 c
ttt 40 a
ttt 5 b
ttt 15 c
vvv 50 a
vvv 70 b
vvv 30 c

Any help would be much appreciated!

thanks

 

[kjv1611]

What have you tried so far?

 

[combo]

Normalize your table with pivot table, see faq68-5287 by Skip Vought.
Alternatively use formulas:
- in column A add numbers: 1, 2, 3... etc. (helper column),
- using MOD and INT functions convert numbers into sequence of data rows (1, 1, 1, 2, 2, 2, 3...) and columns (1, 2, 3, 1, 2, 3...),
- in column B using row index pick value from row headers table (INDEX function),
- in column C using column index pick value from column headers table (INDEX function),
- in column D using row & column index pick value from data table (index function, two arguments).




combo

 

[lionelhill]

Try Skip's FAQ:

How to normalise your data using the pivot table wizard:

http://www.tek-tips.com/faqs.cfm?fid=5287