Expert: Tom Ogilvy - 8/22/2008

Question
I converted thousands data from Word file into Excel; however, is not the format I wanted.

Can you please tell me what formula I can use and make the data look like this

Product   A   
Catalog   25   
Price   2   
     
Product   A   
Catalog   33   
Price   3   
     
Product   D   
Catalog   22   
Price   2   

from this

Product    Catalog   Price   
A   25   2   
A   33   3   
D   22   2   


I have no knowledge about VBA, macro at all.  

I hope to hear from you soon.  

Answer
Assume "Product" is in A1, "Catalog" is in B1 and "Price" is in C1
Assume "A" is in A2, "25" is in B2, and "2" is in C2
and so forth.

Make a copy of the worksheet.
Delete row 1

in D1 put in this formula

=OFFSET($A$1,TRUNC((ROW()-1)/3),MOD(ROW()-1,3))

drag fill it down the column for as far as it produces data.

then select column D and do Edit=>Copy, then Edit=>paste special and select values so the formulas are replace with the values they produced.

Now you can delete Columns A:C.

--
Regards,
Tom Ogilvy