Transpose Horizontal to Vertical with multiple key columns

[Deuteron]

Hello,

I track operational metrics in Excel 2007, and for ease of forecasting, this is laid out with descriptive columns then time frame columns. 72 columns across in total. ~5,300 rows. Sample below.

Each data row has 6 columns that identify the:
A Metric (ie Received Units)
B Ledger (Actual/Forecast)
C Acct # (Metric Acct #)
D Dept# (Physical Location of activity)
E Partner # (client ID #)
F Line Description (client Name)

From here the columns are either months or weeks of the year (we track by fiscal week Sat through Sun) on a 4-4-5 calendar basis.

G Jan
H Feb
...
R Dec
S Total
U Week1
V Week2
...
BT Week 52


Functionally, this works great for forecasting, but stinks for reporting.

I can't figure out how to generate a vertical list with the first 6 columns (A through F above), column G would be the date/week number (G through BT above) and column H would be the data point.

FROM:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Account Description[/TD]
[TD="align: center"] Ledger[/TD]
[TD="align: center"] Account #[/TD]
[TD="align: center"]Dept #[/TD]
[TD="align: center"] Partner #[/TD]
[TD="align: center"]Line Description[/TD]
[TD="align: center"] Jan[/TD]
[TD="align: center"] Feb[/TD]
[TD="align: center"] Mar[/TD]
[TD="align: center"] Apr[/TD]
[TD="align: center"].......[/TD]
[TD="align: center"] Week 52[/TD]
[/TR]
[TR]
[TD="align: center"]Received Units[/TD]
[TD="align: center"]ACTUALS[/TD]
[TD="align: center"] 99002001[/TD]
[TD="align: center"]009999[/TD]
[TD="align: center"]PT.000001[/TD]
[TD="align: center"]CL001[/TD]
[TD="align: center"] 140,802[/TD]
[TD="align: center"] 52,807[/TD]
[TD="align: center"] 53,756[/TD]
[TD="align: center"] 29,335[/TD]
[TD="align: center"].......[/TD]
[TD="align: center"]7,960[/TD]
[/TR]
</tbody>[/TABLE]


TO:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account Description[/TD]
[TD]Ledger[/TD]
[TD]Account #[/TD]
[TD]Dept #[/TD]
[TD]Partner #[/TD]
[TD]Line Description[/TD]
[TD]Timeframe[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Received Units[/TD]
[TD]ACTUALS[/TD]
[TD]99002001[/TD]
[TD]009999[/TD]
[TD]PT.000001[/TD]
[TD]CL001[/TD]
[TD]Jan[/TD]
[TD]140,802[/TD]
[/TR]
[TR]
[TD]Received Units[/TD]
[TD]ACTUALS[/TD]
[TD]99002001[/TD]
[TD]009999[/TD]
[TD]PT.000001[/TD]
[TD]CL001[/TD]
[TD]Feb[/TD]
[TD]52,807[/TD]
[/TR]
[TR]
[TD]Received Units[/TD]
[TD]ACTUALS[/TD]
[TD]99002001[/TD]
[TD]009999[/TD]
[TD]PT.000001[/TD]
[TD]CL001[/TD]
[TD]Mar[/TD]
[TD]53,756[/TD]
[/TR]
[TR]
[TD]Received Units[/TD]
[TD]ACTUALS[/TD]
[TD]99002001[/TD]
[TD]009999[/TD]
[TD]PT.000001[/TD]
[TD]CL001[/TD]
[TD]Apr[/TD]
[TD]29,335[/TD]
[/TR]
[TR]
[TD].......[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Received Units[/TD]
[TD]ACTUALS[/TD]
[TD]99002001[/TD]
[TD]009999[/TD]
[TD]PT.000001[/TD]
[TD]CL001[/TD]
[TD]Week 52[/TD]
[TD]7,960[/TD]
[/TR]
</tbody>[/TABLE]


Again, the data set is 77 total columns and about 5,300 rows. This is replicated across 20 or so separate models.

This data will need to be 'flipped'/'converted' frequently as forecasts and actual data changes.

Help!

Thanks for your time.

 

[QU4487]

Hi,

I think you're looking for something like this;

Installing the Tableau Add-In for Reshaping Data in Excel | Tableau Software

Hope this helps.

Q

 

[Deuteron]

Thanks for the tip - I actually had this and forgot all about it.
I tried it on my data set, and it works to a degree. It bogs down pretty heavily, and for repeatably

I appreciate your time.

Bob