Convert Data in Columns to data in rows - Transcribe doesn't do what I need

[ga1205]

Hello,

I have a workbook with data formatted as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E... BZ[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location[/TD]
[TD]Item[/TD]
[TD]DType[/TD]
[TD]Week1[/TD]
[TD]Week2...[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]ABC[/TD]
[TD]qty[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]100[/TD]
[TD]ABC[/TD]
[TD]rev[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]100[/TD]
[TD]DEF[/TD]
[TD]qty[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]100[/TD]
[TD]DEF[/TD]
[TD]rev[/TD]
[TD]0[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]101[/TD]
[TD]ABC[/TD]
[TD]qty[/TD]
[TD]-1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]7...1MM+[/TD]
[TD]101[/TD]
[TD]ABC[/TD]
[TD]rev[/TD]
[TD]-8.5[/TD]
[TD]42.5[/TD]
[/TR]
</tbody>[/TABLE]

Where the weekly quantity and revenue numbers are in rows and the weeks are in columns. I find this format difficult to work with and would prefer it to be set up as:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location[/TD]
[TD]Item[/TD]
[TD]Week[/TD]
[TD]Qty[/TD]
[TD]Rev[/TD]
[/TR]
</tbody>[/TABLE]

Is there an easy way to make this work? I've got a few tabs with ~1 million rows in each.

Thanks for your hlep!

 

[sheetspread]

The reverse pivot table does this:

https://www.youtube.com/watch?v=xmqTN0X-AgY

 

[ga1205]

The reverse pivot table does this:

https://www.youtube.com/watch?v=xmqTN0X-AgY

Thanks. Looks interesting... not sure it will solve my problem though. A bigger issue is I don't seem to have the Pivot Table Wizard on my version (15.27) of Excel for Mac. Any other way to go about doing this?

Thanks for the help.

 

[sheetspread]

Oh I think it's gone in 2016, let me think of something else

 

[sheetspread]

Do you have Power BI (probably not)?
I don't know how VBA code written in windows would transfer to mac
If you have MS Query in Excel that could be your best option.
Or MS Access or SQL Server