transpose data from columns into rows

Please help.. This is for a bill of materials. Each CPN (Customer part number) has up to 9 matching MPNs (Manufacturer part numbers). All MPNs for a CPN are in a single row.

Need to transpone this:
Item CPN Description | Designators |Quantity |MPN 1 | MAN 1 | MPN 2 | MAN 2 | MPN 3 | MAN 3
1 |a001 |Desc001| 1,2 | 10 |11 | man11 |12 | man12 |13 | m1n13
2 |a002 |Desc002| 7,5 | 30 |14 |man14 | 15 | man15
3 |a003 |Desc003| 8,4 | 40 | 16 |man16


To this (in a new sheet):
(Each of the sets of MAN,MPN are transposed into new rows below, while Columns A:E are copied down)

Line |Item |CPN |Description |Designators| Quantity| MPN |MAN
1 |1 |a001|Desc001 |1,2 | 10 | 11 |man11
2 |1 |a001 |Desc001 |1,2 | 10 | 12 |man12
3 |1 |a001 |Desc001 |1,2 | 10 | 13 |m1n13
4 |2 |a002 |Desc002 |7,5 | 30 | 14 |man14
5 |2 |a002 |Desc002 |7,5 | 30 | 15 |man15
6 |3 |a003 |Desc003 |8,4 | 40 | 16 |man16


It looks better in the attachment

Perfect! does exactly what I need. You've saved us days of work.

One thing though. For the part:

Please Login or Register  to view this content.

I changed it to

Please Login or Register  to view this content.

I need to have as many lines as I want in the pretranspose sheet
It works, but what is the proper way to do it?

Thanks again!

Cross-posted here: Ozgrid