Transpose columns into rows

[Classic-Car-World Ltd]

Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I konw I can use a paste special and transpose option but I have
approximately 5,000 records to sort in this way. Is there a macro or some
code I can use for this?

Thanks in advance for any help


--
Kindest Regards

Tom
Tom McCay (Director)
Classic-Car-World Ltd
Tel: 01522 888178
FAX: 0870 705 9115
E-Mail: (e-mail address removed)
URL: www.classic-car-world.co.uk

Now offering quality Sealey & Draper tools at discount prices, see
www.ccw-tools.com for further details.

 

[Guest]

Try something like this:

With your data list in A17 (including column headings)

F1: Product No
G1: Info_1
H1: Info_2
I1: Info_3

This ARRAY FORMULA* will list each unique Product No
F2:
=IF(SUMPRODUCT(($A$2:$A$11<>"")*ISERROR(MATCH($A$2:$A$11,$F$1:F1,0)))<>0,INDEX($A$2:$A$11,MATCH(TRUE,ISERROR(IF(ISBLANK($A$2:$A$11),FALSE,MATCH($A$2:$A$11,$F$1:$F1,0))),0),1),"")

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy that formula in to F3 and down as far as you need

This regular formula will combine and list the component data
G2:
=INDEX($A$2:$D$11,SUMPRODUCT(MATCH($F2&"_"&COLUMNS($F2:F2),$A$2:$A$11&"_"&$B$2:$B$11,0)),3)&"
"&INDEX($A$2:$D$11,SUMPRODUCT(MATCH($F2&"_"&COLUMNS($F2:F2),$A$2:$A$11&"_"&$B$2:$B$11,0)),4)

Copy that formula across and down as far as you need

Note: Since text wrap will impact the display, those formulas contain NO
spaces.

I know it's not elegant, but is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP