Reverse Pivot - Matrix to record

**PNCD** · 12-02-2009, 11:31 AM

I have a worksheet of attributes in the columns (equity tickers) and rows (dates) with element values in the cells (returns).
I want to turn this matrix into records for loading into Access: Equity, Date, Return along with some other constants.
Does anyone know an easy way to do this in Excel or Access. I am comfortable using VBA. I am running Office 2010 Beta.
Thank you.

**Ron Coderre** · 12-02-2009, 11:35 AM

Can you post some sample data indicating the structure you're starting with along with the ending structure you want?

**PNCD** · 12-02-2009, 12:13 PM

Sure. Not sure if I am supposed to use code tags?

From Excel:
A B C
1 Date SEP AHD
2 11/9/2007 -0.0278 0.0530%
3 11/16/2007 0.0005 -0.0101%

To Access:
Equity Date Return
SEP 11/9/2007 -0.0278
SEP 11/16/2007 0.0005
AHD 11/9/2007 0.0530
AHD 11/16/2007 -0.0101

It is effectively the reverse of a Pivot, which summarizes record-based data into a matrix view.

**DonkeyOte** · 12-02-2009, 12:38 PM

You can use a Multi Consolidation PT to generate the required output.

Begin the PT Wizard (in 2007 easiest to do this via keyboard shortcut if not on your QAT - press ALT + D -> P)

Step1
-- opt for a Multi Consolidation PT

Step 2
-- opt to Crete Page Fields yourself

Step 3
-- add your existing table (so appears in All Ranges)

Click FINISH

On the resulting PT double click on the Grand Total number (bottom right hand corner cell)

You should now find you have the same data transposed (date, code, value) - this sheet is independent of all others so you can manipulate it however you wish without fear of affecting the source data.

EDIT:
If you want a "live" demo see: http://datapigtechnologies.com/flash...osetables.html