How do I take a large 2 dimensional matrix of data and easily create flat
records instead. Cutting and pasting is not an option considering the
original matrix is a huge 2 dimensional spreadsheet. The sample below has
been simplified


Current matrix

ITEM 1/7/2008 1/21/2008 2/4/2008 2/18/2008
Zinnia - mix 111 122 133 104
Petunia - Blue 205 226 27 28
Petunia - Red 399 310 311 312

Desired matrix - flat record

Item Date Quantity
Zinnia - mix 1/7/2008 111
Zinnia - mix 1/21/2008 122
Zinnia - mix 2/4/2008 133
Zinnia - mix 2/18/2008 104
Petunia - Blue 1/7/2008 205
Petunia - Blue 1/21/2008 226
Petunia - Blue 2/4/2008 27
Petunia - Blue 2/18/2008 28
Petunia - Red 1/7/2008 399
Petunia - Red 1/21/2008 310
Petunia - Red 2/4/2008 311
Petunia - Red 2/18/2008 312

Try using this UNpivot method from John Walkenbach's
website (illustrating Joel Horowitz's technique):
http://j-walk.com/ss/excel/usertips/tip068.htm

With your range in this structu
ITEM____________01/7/2008___01/21/2008___02/04/2008__02/18/2008
Zinnia - mix____111_________122__________133__________104
Petunia - Blue__205_________226___________27___________28
Petunia - Red___399_________310__________311__________312

<Data<Pivot Table
Use: Multiple Consolidation Ranges__________Click [Next]
Select: "I will create the page fields"_____Click [Next]
Range: (Select your data)_____Click [Add]___Click [Next]
Click the [Layout] button
ROW: Drag ROW off the diagram
COLUMN: Drag COLUMN off the diagram
DATA: Leave the VALUE field in this section
Click the [OK] button
Select a location for the Pivot Table_____Click [Finish]

That will create a minimal Pivot Table containing only one cell with a
value.

Double-Click on that one value cell
Excel will add a sheet to the workbook with the details of
that cell in a database table format, like this:
Row_____________Column________Value
Zinnia - mix____01/07/2008____111
Zinnia - mix____01/21/2008____122
Zinnia - mix____02/04/2008____133
Zinnia - mix____02/18/2008____104
Petunia - Blue____01/07/2008__205
Petunia - Blue____01/21/2008__226
Petunia - Blue____02/04/2008___27
Petunia - Blue____02/18/2008___28
Petunia - Red____01/07/2008___399
Petunia - Red____01/21/2008___310
Petunia - Red____02/04/2008___311
Petunia - Red____02/18/2008___312

Then change the column headings.

Will that work for you?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Wickliffe" wrote in message
...
How do I take a large 2 dimensional matrix of data and easily create flat
records instead. Cutting and pasting is not an option considering the
original matrix is a huge 2 dimensional spreadsheet. The sample below has
been simplified


Current matrix

ITEM 1/7/2008 1/21/2008 2/4/2008 2/18/2008
Zinnia - mix 111 122 133 104
Petunia - Blue 205 226 27 28
Petunia - Red 399 310 311 312

Desired matrix - flat record

Item Date Quantity
Zinnia - mix 1/7/2008 111
Zinnia - mix 1/21/2008 122
Zinnia - mix 2/4/2008 133
Zinnia - mix 2/18/2008 104
Petunia - Blue 1/7/2008 205
Petunia - Blue 1/21/2008 226
Petunia - Blue 2/4/2008 27
Petunia - Blue 2/18/2008 28
Petunia - Red 1/7/2008 399
Petunia - Red 1/21/2008 310
Petunia - Red 2/4/2008 311
Petunia - Red 2/18/2008 312

It worked
You are kind to share this quick tip
Saved me hours

Wick


"Ron Coderre" wrote:

Try using this UNpivot method from John Walkenbach's
website (illustrating Joel Horowitz's technique):
http://j-walk.com/ss/excel/usertips/tip068.htm

With your range in this structu
ITEM____________01/7/2008___01/21/2008___02/04/2008__02/18/2008
Zinnia - mix____111_________122__________133__________104
Petunia - Blue__205_________226___________27___________28
Petunia - Red___399_________310__________311__________312

<Data<Pivot Table
Use: Multiple Consolidation Ranges__________Click [Next]
Select: "I will create the page fields"_____Click [Next]
Range: (Select your data)_____Click [Add]___Click [Next]
Click the [Layout] button
ROW: Drag ROW off the diagram
COLUMN: Drag COLUMN off the diagram
DATA: Leave the VALUE field in this section
Click the [OK] button
Select a location for the Pivot Table_____Click [Finish]

That will create a minimal Pivot Table containing only one cell with a
value.

Double-Click on that one value cell
Excel will add a sheet to the workbook with the details of
that cell in a database table format, like this:
Row_____________Column________Value
Zinnia - mix____01/07/2008____111
Zinnia - mix____01/21/2008____122
Zinnia - mix____02/04/2008____133
Zinnia - mix____02/18/2008____104
Petunia - Blue____01/07/2008__205
Petunia - Blue____01/21/2008__226
Petunia - Blue____02/04/2008___27
Petunia - Blue____02/18/2008___28
Petunia - Red____01/07/2008___399
Petunia - Red____01/21/2008___310
Petunia - Red____02/04/2008___311
Petunia - Red____02/18/2008___312

Then change the column headings.

Will that work for you?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Wickliffe" wrote in message
...
How do I take a large 2 dimensional matrix of data and easily create flat
records instead. Cutting and pasting is not an option considering the
original matrix is a huge 2 dimensional spreadsheet. The sample below has
been simplified


Current matrix

ITEM 1/7/2008 1/21/2008 2/4/2008 2/18/2008
Zinnia - mix 111 122 133 104
Petunia - Blue 205 226 27 28
Petunia - Red 399 310 311 312

Desired matrix - flat record

Item Date Quantity
Zinnia - mix 1/7/2008 111
Zinnia - mix 1/21/2008 122
Zinnia - mix 2/4/2008 133
Zinnia - mix 2/18/2008 104
Petunia - Blue 1/7/2008 205
Petunia - Blue 1/21/2008 226
Petunia - Blue 2/4/2008 27
Petunia - Blue 2/18/2008 28
Petunia - Red 1/7/2008 399
Petunia - Red 1/21/2008 310
Petunia - Red 2/4/2008 311
Petunia - Red 2/18/2008 312