splitting data in a column

I have an excel spreadsheets with the following columns:
Product Equipment
6OL 1702/1701
6OL/LHH 1716
LHH/6OL/SOL 1720
SOL 1701 To 1716

I need to convert it as follow:
SOL 1701 To 1716
translate to
SOL 1701
SOL 1702
.......
SOL 1716

LHH/6OL/SOL 1720
map to
LHH 1720
6OL 1720
SOL 1720
etc.
I was advised to do the following thing (thanks very much to Tim Williams):
Create a couple of generic functions, one to split "/"-separated cell
values, the other to split cells values containing "TO". Both would
return arrays: in the second case (X to Y) it would be an array of all
numbers from the first to the second number.

Once you have these you can parse each pair of cells into two arrays
and list all combinations of the two arrays. If you only want
distinct combinations then just sort the list and extract the unique
pairs.

But, the problem is I don't know how to use all this. I answered about it
but the topic was sunk with the old date.

Could anybody advise how to implement those splittings?

Thanks

Will you have initial combinations on the same line like:

LHH/6OL/SOL 1701 To 1716

It makes a big difference, so I am asking.


"Alex" <[email protected]> wrote in message
news:[email protected]...
>I have an excel spreadsheets with the following columns:
> Product Equipment
> 6OL 1702/1701
> 6OL/LHH 1716
> LHH/6OL/SOL 1720
> SOL 1701 To 1716
>
> I need to convert it as follow:
> SOL 1701 To 1716
> translate to
> SOL 1701
> SOL 1702
> ......
> SOL 1716
>
> LHH/6OL/SOL 1720
> map to
> LHH 1720
> 6OL 1720
> SOL 1720
> etc.
> I was advised to do the following thing (thanks very much to Tim
> Williams):
> Create a couple of generic functions, one to split "/"-separated cell
> values, the other to split cells values containing "TO". Both would
> return arrays: in the second case (X to Y) it would be an array of all
> numbers from the first to the second number.
>
> Once you have these you can parse each pair of cells into two arrays
> and list all combinations of the two arrays. If you only want
> distinct combinations then just sort the list and extract the unique
> pairs.
>
> But, the problem is I don't know how to use all this. I answered about it
> but the topic was sunk with the old date.
>
> Could anybody advise how to implement those splittings?
>
> Thanks
>
>
>

Yes, it's a very seldom case (theoretically, it's possible). May be we can
omit it to start at least with something. But, the similar combination as
follows exists:
Product Equipment
LHH/6OL 1701/1702/1705/1716

It should be converted to
LHH 1701
LHH 1702
....
6OL 1701
6OL 1702
....

Thanks

"William Benson" wrote:

> Will you have initial combinations on the same line like:
>
> LHH/6OL/SOL 1701 To 1716
>
> It makes a big difference, so I am asking.
>
>
> "Alex" <[email protected]> wrote in message
> news:[email protected]...
> >I have an excel spreadsheets with the following columns:
> > Product Equipment
> > 6OL 1702/1701
> > 6OL/LHH 1716
> > LHH/6OL/SOL 1720
> > SOL 1701 To 1716
> >
> > I need to convert it as follow:
> > SOL 1701 To 1716
> > translate to
> > SOL 1701
> > SOL 1702
> > ......
> > SOL 1716
> >
> > LHH/6OL/SOL 1720
> > map to
> > LHH 1720
> > 6OL 1720
> > SOL 1720
> > etc.
> > I was advised to do the following thing (thanks very much to Tim
> > Williams):
> > Create a couple of generic functions, one to split "/"-separated cell
> > values, the other to split cells values containing "TO". Both would
> > return arrays: in the second case (X to Y) it would be an array of all
> > numbers from the first to the second number.
> >
> > Once you have these you can parse each pair of cells into two arrays
> > and list all combinations of the two arrays. If you only want
> > distinct combinations then just sort the list and extract the unique
> > pairs.
> >
> > But, the problem is I don't know how to use all this. I answered about it
> > but the topic was sunk with the old date.
> >
> > Could anybody advise how to implement those splittings?
> >
> > Thanks
> >
> >
> >
>
>
>

Alex,

Try this - very simple and no error checking: will only parse
"/"-delimited values and values like "XXX to YYY"

Place list of value pairs beginning at C8: unique combinations listed
beginning at F2

Tim.



Option Explicit


Sub Parse()

Dim rStart As Range, m, n
Dim arrA As Variant, arrB As Variant
Dim iRow As Long


Set rStart = ActiveSheet.Range("C8")
iRow = 2

Do While rStart.Value <> ""

arrA = Expand(Trim(rStart.Value))
arrB = Expand(Trim(rStart.Offset(0, 1).Value))

For m = LBound(arrA) To UBound(arrA)
For n = LBound(arrB) To UBound(arrB)

ActiveSheet.Cells(iRow, 6).Value = arrA(m)
ActiveSheet.Cells(iRow, 7).Value = arrB(n)
iRow = iRow + 1

Next n
Next m

Set rStart = rStart.Offset(1, 0)
Loop

End Sub

Function Expand(vIn As String) As Variant
If InStr(1, vIn, "TO", vbTextCompare) > 0 Then
Expand = ExpandTo(vIn)
Else
Expand = Split(vIn, "/")
End If
End Function

Function ExpandTo(vIn As String) As Variant

Dim arr
Dim vals As String
Dim m As Long, n As Long, i As Long

vals = ""

arr = Split(UCase(vIn), "TO")
m = CLng(Trim(arr(0)))
n = CLng(Trim(arr(1)))

For i = m To n
vals = IIf(vals <> "", vals & "~", vals) & CStr(i)
Next i

ExpandTo = Split(vals, "~")
End Function





"Alex" <[email protected]> wrote in message
news:[email protected]...
> Yes, it's a very seldom case (theoretically, it's possible). May be
> we can
> omit it to start at least with something. But, the similar
> combination as
> follows exists:
> Product Equipment
> LHH/6OL 1701/1702/1705/1716
>
> It should be converted to
> LHH 1701
> LHH 1702
> ...
> 6OL 1701
> 6OL 1702
> ...
>
> Thanks
>
> "William Benson" wrote:
>
>> Will you have initial combinations on the same line like:
>>
>> LHH/6OL/SOL 1701 To 1716
>>
>> It makes a big difference, so I am asking.
>>
>>
>> "Alex" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have an excel spreadsheets with the following columns:
>> > Product Equipment
>> > 6OL 1702/1701
>> > 6OL/LHH 1716
>> > LHH/6OL/SOL 1720
>> > SOL 1701 To 1716
>> >
>> > I need to convert it as follow:
>> > SOL 1701 To 1716
>> > translate to
>> > SOL 1701
>> > SOL 1702
>> > ......
>> > SOL 1716
>> >
>> > LHH/6OL/SOL 1720
>> > map to
>> > LHH 1720
>> > 6OL 1720
>> > SOL 1720
>> > etc.
>> > I was advised to do the following thing (thanks very much to Tim
>> > Williams):
>> > Create a couple of generic functions, one to split "/"-separated
>> > cell
>> > values, the other to split cells values containing "TO". Both
>> > would
>> > return arrays: in the second case (X to Y) it would be an array
>> > of all
>> > numbers from the first to the second number.
>> >
>> > Once you have these you can parse each pair of cells into two
>> > arrays
>> > and list all combinations of the two arrays. If you only want
>> > distinct combinations then just sort the list and extract the
>> > unique
>> > pairs.
>> >
>> > But, the problem is I don't know how to use all this. I answered
>> > about it
>> > but the topic was sunk with the old date.
>> >
>> > Could anybody advise how to implement those splittings?
>> >
>> > Thanks
>> >
>> >
>> >
>>
>>
>>

I hope your effort is appreciated, it is a beautiful solution and has taught
me a lot - thanks.


"Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
news:Ook2FB%[email protected]...
> Alex,
>
> Try this - very simple and no error checking: will only parse
> "/"-delimited values and values like "XXX to YYY"
>
> Place list of value pairs beginning at C8: unique combinations listed
> beginning at F2
>
> Tim.
>
>
>
> Option Explicit
>
>
> Sub Parse()
>
> Dim rStart As Range, m, n
> Dim arrA As Variant, arrB As Variant
> Dim iRow As Long
>
>
> Set rStart = ActiveSheet.Range("C8")
> iRow = 2
>
> Do While rStart.Value <> ""
>
> arrA = Expand(Trim(rStart.Value))
> arrB = Expand(Trim(rStart.Offset(0, 1).Value))
>
> For m = LBound(arrA) To UBound(arrA)
> For n = LBound(arrB) To UBound(arrB)
>
> ActiveSheet.Cells(iRow, 6).Value = arrA(m)
> ActiveSheet.Cells(iRow, 7).Value = arrB(n)
> iRow = iRow + 1
>
> Next n
> Next m
>
> Set rStart = rStart.Offset(1, 0)
> Loop
>
> End Sub
>
> Function Expand(vIn As String) As Variant
> If InStr(1, vIn, "TO", vbTextCompare) > 0 Then
> Expand = ExpandTo(vIn)
> Else
> Expand = Split(vIn, "/")
> End If
> End Function
>
> Function ExpandTo(vIn As String) As Variant
>
> Dim arr
> Dim vals As String
> Dim m As Long, n As Long, i As Long
>
> vals = ""
>
> arr = Split(UCase(vIn), "TO")
> m = CLng(Trim(arr(0)))
> n = CLng(Trim(arr(1)))
>
> For i = m To n
> vals = IIf(vals <> "", vals & "~", vals) & CStr(i)
> Next i
>
> ExpandTo = Split(vals, "~")
> End Function
>
>
>
>
>
> "Alex" <[email protected]> wrote in message
> news:[email protected]...
>> Yes, it's a very seldom case (theoretically, it's possible). May be we
>> can
>> omit it to start at least with something. But, the similar combination as
>> follows exists:
>> Product Equipment
>> LHH/6OL 1701/1702/1705/1716
>>
>> It should be converted to
>> LHH 1701
>> LHH 1702
>> ...
>> 6OL 1701
>> 6OL 1702
>> ...
>>
>> Thanks
>>
>> "William Benson" wrote:
>>
>>> Will you have initial combinations on the same line like:
>>>
>>> LHH/6OL/SOL 1701 To 1716
>>>
>>> It makes a big difference, so I am asking.
>>>
>>>
>>> "Alex" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >I have an excel spreadsheets with the following columns:
>>> > Product Equipment
>>> > 6OL 1702/1701
>>> > 6OL/LHH 1716
>>> > LHH/6OL/SOL 1720
>>> > SOL 1701 To 1716
>>> >
>>> > I need to convert it as follow:
>>> > SOL 1701 To 1716
>>> > translate to
>>> > SOL 1701
>>> > SOL 1702
>>> > ......
>>> > SOL 1716
>>> >
>>> > LHH/6OL/SOL 1720
>>> > map to
>>> > LHH 1720
>>> > 6OL 1720
>>> > SOL 1720
>>> > etc.
>>> > I was advised to do the following thing (thanks very much to Tim
>>> > Williams):
>>> > Create a couple of generic functions, one to split "/"-separated cell
>>> > values, the other to split cells values containing "TO". Both would
>>> > return arrays: in the second case (X to Y) it would be an array of all
>>> > numbers from the first to the second number.
>>> >
>>> > Once you have these you can parse each pair of cells into two arrays
>>> > and list all combinations of the two arrays. If you only want
>>> > distinct combinations then just sort the list and extract the unique
>>> > pairs.
>>> >
>>> > But, the problem is I don't know how to use all this. I answered about
>>> > it
>>> > but the topic was sunk with the old date.
>>> >
>>> > Could anybody advise how to implement those splittings?
>>> >
>>> > Thanks
>>> >
>>> >
>>> >
>>>
>>>
>>>
>
>

Thanks a lot , Tim.
It's working perfectly.

"Tim Williams" wrote:

> Alex,
>
> Try this - very simple and no error checking: will only parse
> "/"-delimited values and values like "XXX to YYY"
>
> Place list of value pairs beginning at C8: unique combinations listed
> beginning at F2
>
> Tim.
>
>
>
> Option Explicit
>
>
> Sub Parse()
>
> Dim rStart As Range, m, n
> Dim arrA As Variant, arrB As Variant
> Dim iRow As Long
>
>
> Set rStart = ActiveSheet.Range("C8")
> iRow = 2
>
> Do While rStart.Value <> ""
>
> arrA = Expand(Trim(rStart.Value))
> arrB = Expand(Trim(rStart.Offset(0, 1).Value))
>
> For m = LBound(arrA) To UBound(arrA)
> For n = LBound(arrB) To UBound(arrB)
>
> ActiveSheet.Cells(iRow, 6).Value = arrA(m)
> ActiveSheet.Cells(iRow, 7).Value = arrB(n)
> iRow = iRow + 1
>
> Next n
> Next m
>
> Set rStart = rStart.Offset(1, 0)
> Loop
>
> End Sub
>
> Function Expand(vIn As String) As Variant
> If InStr(1, vIn, "TO", vbTextCompare) > 0 Then
> Expand = ExpandTo(vIn)
> Else
> Expand = Split(vIn, "/")
> End If
> End Function
>
> Function ExpandTo(vIn As String) As Variant
>
> Dim arr
> Dim vals As String
> Dim m As Long, n As Long, i As Long
>
> vals = ""
>
> arr = Split(UCase(vIn), "TO")
> m = CLng(Trim(arr(0)))
> n = CLng(Trim(arr(1)))
>
> For i = m To n
> vals = IIf(vals <> "", vals & "~", vals) & CStr(i)
> Next i
>
> ExpandTo = Split(vals, "~")
> End Function
>
>
>
>
>
> "Alex" <[email protected]> wrote in message
> news:[email protected]...
> > Yes, it's a very seldom case (theoretically, it's possible). May be
> > we can
> > omit it to start at least with something. But, the similar
> > combination as
> > follows exists:
> > Product Equipment
> > LHH/6OL 1701/1702/1705/1716
> >
> > It should be converted to
> > LHH 1701
> > LHH 1702
> > ...
> > 6OL 1701
> > 6OL 1702
> > ...
> >
> > Thanks
> >
> > "William Benson" wrote:
> >
> >> Will you have initial combinations on the same line like:
> >>
> >> LHH/6OL/SOL 1701 To 1716
> >>
> >> It makes a big difference, so I am asking.
> >>
> >>
> >> "Alex" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have an excel spreadsheets with the following columns:
> >> > Product Equipment
> >> > 6OL 1702/1701
> >> > 6OL/LHH 1716
> >> > LHH/6OL/SOL 1720
> >> > SOL 1701 To 1716
> >> >
> >> > I need to convert it as follow:
> >> > SOL 1701 To 1716
> >> > translate to
> >> > SOL 1701
> >> > SOL 1702
> >> > ......
> >> > SOL 1716
> >> >
> >> > LHH/6OL/SOL 1720
> >> > map to
> >> > LHH 1720
> >> > 6OL 1720
> >> > SOL 1720
> >> > etc.
> >> > I was advised to do the following thing (thanks very much to Tim
> >> > Williams):
> >> > Create a couple of generic functions, one to split "/"-separated
> >> > cell
> >> > values, the other to split cells values containing "TO". Both
> >> > would
> >> > return arrays: in the second case (X to Y) it would be an array
> >> > of all
> >> > numbers from the first to the second number.
> >> >
> >> > Once you have these you can parse each pair of cells into two
> >> > arrays
> >> > and list all combinations of the two arrays. If you only want
> >> > distinct combinations then just sort the list and extract the
> >> > unique
> >> > pairs.
> >> >
> >> > But, the problem is I don't know how to use all this. I answered
> >> > about it
> >> > but the topic was sunk with the old date.
> >> >
> >> > Could anybody advise how to implement those splittings?
> >> >
> >> > Thanks
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>