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:

Account Description	Ledger	Account #	Dept #	Partner #	Line Description	Jan	Feb	Mar	Apr	.......	Week 52
Received Units	ACTUALS	99002001	009999	PT.000001	CL001	140,802	52,807	53,756	29,335	.......	7,960

<tbody>
</tbody>

TO:

Account Description	Ledger	Account #	Dept #	Partner #	Line Description	Timeframe	Data
Received Units	ACTUALS	99002001	009999	PT.000001	CL001	Jan	140,802
Received Units	ACTUALS	99002001	009999	PT.000001	CL001	Feb	52,807
Received Units	ACTUALS	99002001	009999	PT.000001	CL001	Mar	53,756
Received Units	ACTUALS	99002001	009999	PT.000001	CL001	Apr	29,335
.......
Received Units	ACTUALS	99002001	009999	PT.000001	CL001	Week 52	7,960

<tbody>
</tbody>

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