I have a spreadsheet that has several rows of data varying in length.
Everything under the first column is a part number then everything to
the right of the part number in each row is a product code. I need
something that will convert this:

PN PROD PROD PROD PROD PROD PROD PROD

12345 rq545 gr433 or444 tz222 hq444 pl111
67892 ca544 ca899 cb355 ay111
98765 dd777 hw345 pp943 qa612 tw555 uj764 vf544
58992 gh678 lo122 uo512 mm677 xx411

INTO:

PN PROD
12345 rq545
12345 gr433
12345 or444
12345 tz222
12345 hq444
12345 pl111
67892 ca544
67892 ca899
67892 cb355
67892 ay111
98765 dd777
98765 hw345
98765 pp943
98765 qa612
98765 tw555
98765 uj764
98765 vf544
58992 gh678
58992 lo122
58992 uo512
58992 mm677
58992 xx411


except on a much larger scale of data....

any help would be appreciated.....Thanks, Steve

One play ..

Assume this source table is in Sheet1, cols A to H, data from row2 down

PN PROD PROD PROD PROD PROD PROD PROD
12345 rq545 gr433 or444 tz222 hq444 pl111
67892 ca544 ca899 cb355 ay111
98765 dd777 hw345 pp943 qa612 tw555 uj764 vf544
58992 gh678 lo122 uo512 mm677 xx411

In Sheet2
---
Put in A2:
=OFFSET(Sheet1!$A$2,INT((ROWS(Sheet1!$A$1:A1)-1)/7),)

Put in B2:
=OFFSET(Sheet1!$B$2,INT((ROWS(Sheet1!$A$1:A1)-1)/7),MOD(ROWS(Sheet1!$A$1:A1)
-1,7))

Note: Change the number "7" within both OFFSET formulas in A2 & B2 above to
be equal to the maximum number of Product code cols that you have in Sheet1.
I used "7" because the sample data had a max of 7 product code cols

Select A2:B2, fill down until zeros appear in *both* cols A & B, signalling
exhaustion of data "extraction" from Sheet1

Kill the formulas in cols A & B with an in-place:
Copy paste special values ok

Put labels into A1:B1, do a Data Filter Autofilter
In the autofilter droplist in B1, select: 0
Then select all the filtered rows (all blue row headers),
Right-click Delete Row

Remove the autofilter, and the desired results
will be left in cols A & B ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Steve" wrote in message
ups.com...
I have a spreadsheet that has several rows of data varying in length.
Everything under the first column is a part number then everything to
the right of the part number in each row is a product code. I need
something that will convert this:

PN PROD PROD PROD PROD PROD PROD PROD
12345 rq545 gr433 or444 tz222 hq444 pl111
67892 ca544 ca899 cb355 ay111
98765 dd777 hw345 pp943 qa612 tw555 uj764 vf544
58992 gh678 lo122 uo512 mm677 xx411

INTO:

PN PROD
12345 rq545
12345 gr433
12345 or444
12345 tz222
12345 hq444
12345 pl111
67892 ca544
67892 ca899
67892 cb355
67892 ay111
98765 dd777
98765 hw345
98765 pp943
98765 qa612
98765 tw555
98765 uj764
98765 vf544
58992 gh678
58992 lo122
58992 uo512
58992 mm677
58992 xx411


except on a much larger scale of data....

any help would be appreciated.....Thanks, Steve

worked like a charm, thanks a lot, Steve....

Glad to hear that, Steve !
You're welcome ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Steve" wrote in message
oups.com...
worked like a charm, thanks a lot, Steve....