Mornin' all,

I've got a table with company details on it, then a list
of product codes that the company sells.
I want to write a new table, for the moment just a new
worksheet would be great, based on the product codes.

I'd like to take a record like this (where the ' - ' bit
specifies a new column):

R1FQ33 - 20780P,41930P
R7D41L - 52100S,84500S,42230P,45210P

and turn it into this:

R1FQ33 - 20780 - P
R1FQ33 - 41930 - P
R7D41L - 52100 - S
R7D41L - 84500 - S
R7D41L - 42230 - P
R7D41L - 45210 - P

There can be any number of items in the second column but they're always
separated by a comma. Each item in the cell can be four or five numbers
followed by a letter.

I can't get my head round how I'm going to do this. Let alone make it work,
can anyone offer any sample code or pointers?

Thanks very much,
Foss

If you have xl2000 or later

Sub WriteData()
j = 0
set rng = Range(Cells(2,1),Cells(2,1).End(xldown)
set rng1 = selection.parent.next.Range("A2")
for each cell in rng
sID = cell.Value
v = Split(cell.offset(0,1).Value,",")
for i = lbound(v) to ubound(v)
rng1.offset(j,0).Value = sID
rng1.offset(j,1).Value = "'" & left(v(i),len(v(i))-1)
rng1.offset(j,2).Value = Right(v(i),1)
j = j + 1
Next i
Next cell
End Sub

Select the sheet with you numbers as the active sheet. The results are
written to the next sheet in the tab order (so this should be blank).

It starts in A2 of each sheet (adjust to suit). It puts the numbers in as
Text so you don't lose any leading zeros. If you want them stored as
numbers, then remove the "'" & from the code above.

--
Regards,
Tom Ogilvy


"Foss" wrote in message
...
Mornin' all,

I've got a table with company details on it, then a list
of product codes that the company sells.
I want to write a new table, for the moment just a new
worksheet would be great, based on the product codes.

I'd like to take a record like this (where the ' - ' bit
specifies a new column):

R1FQ33 - 20780P,41930P
R7D41L - 52100S,84500S,42230P,45210P

and turn it into this:

R1FQ33 - 20780 - P
R1FQ33 - 41930 - P
R7D41L - 52100 - S
R7D41L - 84500 - S
R7D41L - 42230 - P
R7D41L - 45210 - P

There can be any number of items in the second column but they're always
separated by a comma. Each item in the cell can be four or five numbers
followed by a letter.

I can't get my head round how I'm going to do this. Let alone make it
work,
can anyone offer any sample code or pointers?

Thanks very much,
Foss

Tom,
you've beat me to it..

Sub ParseAndDump()
Dim rowSrc As Range
Dim rngDst As Range
Dim itm As Variant
Dim r As Long
'adjust Source and destination to suit...
Set rngDst = Worksheets(2).Cells(1)
For Each rowSrc In Worksheets(1).Cells(1).CurrentRegion.Rows
For Each itm In Split(rowSrc.Cells(1, 2), ",")
rngDst.Offset(r).Resize(1, 3) = Array( _
rowSrc.Cells(1, 1), Val(itm), Right(itm, 1))
r = r + 1
Next
Next

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

If you have xl2000 or later

Sub WriteData()
j = 0
set rng = Range(Cells(2,1),Cells(2,1).End(xldown)
set rng1 = selection.parent.next.Range("A2")
for each cell in rng
sID = cell.Value
v = Split(cell.offset(0,1).Value,",")
for i = lbound(v) to ubound(v)
rng1.offset(j,0).Value = sID
rng1.offset(j,1).Value = "'" & left(v(i),len(v(i))-1)
rng1.offset(j,2).Value = Right(v(i),1)
j = j + 1
Next i
Next cell
End Sub

Select the sheet with you numbers as the active sheet. The results
are written to the next sheet in the tab order (so this should be
blank).

It starts in A2 of each sheet (adjust to suit). It puts the numbers
in as Text so you don't lose any leading zeros. If you want them
stored as numbers, then remove the "'" & from the code above.

As ever, you've helped me no end!

Thanks very much to both of you. Much appreciated!

Cheers,
Pete

"Foss" wrote:

Mornin' all,

I've got a table with company details on it, then a list
of product codes that the company sells.
I want to write a new table, for the moment just a new
worksheet would be great, based on the product codes.

I'd like to take a record like this (where the ' - ' bit
specifies a new column):

R1FQ33 - 20780P,41930P
R7D41L - 52100S,84500S,42230P,45210P

and turn it into this:

R1FQ33 - 20780 - P
R1FQ33 - 41930 - P
R7D41L - 52100 - S
R7D41L - 84500 - S
R7D41L - 42230 - P
R7D41L - 45210 - P

There can be any number of items in the second column but they're always
separated by a comma. Each item in the cell can be four or five numbers
followed by a letter.

I can't get my head round how I'm going to do this. Let alone make it work,
can anyone offer any sample code or pointers?

Thanks very much,
Foss