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

splitting data in a column
 

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" wrote in message
...
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

splitting data in a column
 

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" wrote in message
...
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

splitting data in a column
 

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" wrote in message
...
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" wrote in message
...
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

splitting data in a column
 

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
...
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" wrote in message
...
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" wrote in message
...
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

splitting data in a column
 

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" wrote in message
...
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" wrote in message
...
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