To convert data in multiple sets of columns to multiple rows

[Dhanya_nair]

Hello All,
I have data in the following format:

No. Comp.1 Mfg Mfg Loc. Distr. Distr. Loc Comp. 2 Mfg Mfg Loc. Distr. Distr. Loc
389 Carton TEST 1 Location 1 TEST2 Location 2 Insert TEST3 Location 3 TEST4 Location 4
390 Carton TEST 1 Location 1 TEST2 Location 2 Insert TEST3 Location 3 TEST4 Location 4
391 Carton TEST 1 Location 1 TEST2 Location 2 Insert TEST3 Location 3 TEST4 Location 4

and i need to convert to the below format:

No. Comp. Mfg Mfg Loc. Distr. Distr. Loc
389 Carton TEST 1 Location 1 TEST2 Location 2
389 Insert TEST3 Location 3 TEST4 Location 4
390 Carton TEST 1 Location 1 TEST2 Location 2
390 Insert TEST3 Location 3 TEST4 Location 4
391 Carton TEST 1 Location 1 TEST2 Location 2
391 Insert TEST3 Location 3 TEST4 Location 4


how can i code to get this done in excel as i have 15 such component sets..so each No. will have 15 rows with its associated data.
i have attached excel for the above mentioned data.

[JieJenn]

Try this (knock on wood)

Please Login or Register  to view this content.