Transpose Data.....I think

[brandon16]

Hi Guy's

Any idea how to I turn my data output from this....

	201309	201310	201311	201312	201401
A	13000	18266	15300	7250	25050
B	0	15568.9904	15000	16900	47040
C	0	0	0	8750	126082.4

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

to this very quickly please?

A	201309	13000
B	201309	0
C	201309	0
A	201310	18266
B	201310	15569
C	201310	0
A	201311	15300
B	201311	15000
C	201311	0
A	201312	7250
B	201312	16900
C	201312	8750
A	201401	25050
B	201401	47040
C	201401	126082

<colgroup><col><col><col></colgroup><tbody>
</tbody>

At the moment I am having to do it manually and I have a lot of data, I am hoping there is a quick win.

Thanks

 

[brandon16]

Hi Rick,

I can get the date to work using the 2nd formula but the 3rd forumla for the value doesn't.

I am starting this cell E2 and the variable ABC (dummy data) which is 33 rows with 92 columns. What parts of the forumula do I need to change please?

=INDEX($F$2:$ZZ$2,MOD(ROWS($1:1)-1,3)+1,INT((ROWS($1:1)-1)/34)+1)

 

[brandon16]

a

 

[hiker95]

I am starting this cell E2 and the variable ABC (dummy data) which is 33 rows with 92 columns.

brandon16,

So that we can get it right on the next try, can we see your actual raw data workbook/worksheet(s), and, can we see what the results (manually formatted by you) should look like?


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com