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 structure:
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" <(E-Mail Removed)> wrote in message
news:1A655F4C-3105-4DD2-842F-(E-Mail Removed)...
> 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
>
Similar Threads
