Automatic searchingAND COPYING

[vinod_nir71]

I have the following problem. I have the following data I want to copy an entire row in the different sheet with different type of data. some data fields are merged as well.
So i find it difficult to convert in the required format
for example i have the data as

1CO1

1CO2

1CO3

1CG1

<tbody>
</tbody>

2	UMA003 L
	F102							HG

<tbody>
</tbody>

I want the following

2 UMA003L F102 HG 1CO1
2 UMA003L F102 HG 1CO2
2 UMA003L F102 HG 1CO3
2 UMA003L F102 HG 1CG1

1CO1 ,1CO2,1CO2,1CG1 ARE COLUMN HEADING . I WANT ABOVE DATA IN ANOTHER SHEET