Hello everyone,
I would like to perform a transpose but I do not know how to do it properly.
Intially I have this table:
Company | M01 | M02 | M03 | M04 | M05 | M06 |
A | 1 | 2 | 3 | 4 | 5 | 6 |
B | 2 | 3 | 5 | 1 | 2 | 4 |
C | 3 | 5 | 7 | 8 | 9 | 10 |
D | 1 | 1 | 3 | 3 | 1 | 2 |
I would like to tranpose it to the table below:
Time | A | B | C | D |
M01 | 1 | ... | ||
M02 | 2 | ... | ||
M03 | 3 | ... | ||
M04 | 4 | ... |
Thank you in advance for your help
Solved! Go to Solution.
Hi @national,
You'll want to first Transpose the data, then Cross Tab into the desired field structure:
Nice work @Aaron_Harter
If you TRANSPOSE the data, Group by company then you will get:
Company, Name, value.
next: CROSS TAB and again group by company. You will set the header to Name and value to value. You need to select a method. Just select FIRST. Your data should be unique and it won't matter.
your data should be good to go. The columns will be alphabetical and if a space occurs in the company name, it will be replaced by an underscore.
a dynamic rename tool with a formula to replace the field Name for all underscores with a space will solve that.
cheers,
mark