Reorganize excel listing

[Guest]

I need to develop a product/order list from an existing list in a different format. I have attached both. Is there VBA code that can do this for me.??

CustNo P1 P2 P3 P4 P5 P6 P7
A1234 15 18
A3245 10
B3456
D3459 23 35

From the above sheet, I need code to create listing as follows:

On the first sheet the field names P1,P2,P3…..are product code
CustNo ProdCode Quantity
A1234 P1 15
A1234 P3 18
A3245 P5 10
D3459 P2 23
D3459 P7 35


Thanks for any ideas.

 

[Debra Dalgleish]

To reorganize the data, you can use the "unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm