rearrange date

[tuanvcb]

I have a data range for product output for 2001,2002...as follows :
product 2001 2002
a 10 20
b 30 40
..
I want to rearrange the data by putting all years in one column as follows :
product
a 10 2001
b 30 2001
a 20 2002
b 40 2002
..
Yes, I can do that by using cut and paste method. But it really takes ime if
the number of years in the above example is not limited to 2 but 10 or 20 or
more
..
My question is : How can I rearrange data as above by using another method
that is faster instead of using cut and paste method
..
thanks a lot

 

[Ron Coderre]

Your structure lends itself to the UNpivot technique explained in John
Walkenbach's website. Unpivoting the data will create a regular table that
can be autofiltered.

You already have column headings: Product, 2001, 2002

From John Walkenbach's website illustrating Joel Horowitz's technique:
http://j-walk.com/ss/excel/usertips/tip068.htm

<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
a___2001___10
a___2002___20
b___2001___30
b___2002___40

Does that help?
Post back if you have more questions.
--------------------------

Regards,

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