Transpose Data.....I think

[brandon16]

Hi Guy's

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

[TABLE="width: 436"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: right"]201309[/TD]
[TD="align: right"]201310[/TD]
[TD="align: right"]201311[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]201401[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]13000[/TD]
[TD="align: right"]18266[/TD]
[TD="align: right"]15300[/TD]
[TD="align: right"]7250[/TD]
[TD="align: right"]25050[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15568.9904[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]16900[/TD]
[TD="align: right"]47040[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8750[/TD]
[TD="align: right"]126082.4[/TD]
[/TR]
</tbody>[/TABLE]


to this very quickly please?

[TABLE="width: 244"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD="align: right"]201309[/TD]
[TD="align: right"]13000[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201309[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201309[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]201310[/TD]
[TD="align: right"]18266[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201310[/TD]
[TD="align: right"]15569[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201310[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]201311[/TD]
[TD="align: right"]15300[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201311[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201311[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]7250[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]16900[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]8750[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]201401[/TD]
[TD="align: right"]25050[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201401[/TD]
[TD="align: right"]47040[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201401[/TD]
[TD="align: right"]126082[/TD]
[/TR]
</tbody>[/TABLE]

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