How best to parse string lists of numbers

[TFTAJLLYMXZP]

Hi,

I have a data source that will be providing string values made up of
individual 1,2, or 3-digit numbers separated by spaces, ranges of
numbers indicated by a dash, or a mixture of both. There are no
repeat or overlapping values, and the numbers may range from 1 to 100.

For example, the string might look like this

"1 6-9 11 16-19 21 26-29"

The goal is to count how many of these numbers fall into one of ten
ranges of numbers, i.e. 1-10, 11-20, ... 91-100, get a lookup value
based on the range, and multiply that lookup value by the count of
numbers within the range.

My first thought was to parse the string into a single-dimension array
holding the individual numbers so I can later loop through the array
in order to get the lookup value, but would appreciate any suggestions
for making this more efficient.

Thanks very much,

Terry

 

[joel]

I would use 4 columns on the worksheet

String Lower Number Upper Number Look up Value
A B C D


A1 1-10

MyStr = Range("A1")
Lower = Val(left(MyStr,Instr(MyStr,"-")-1))
if Instr(Mystr,"-") = 0 then
Upper = Lower
else
Upper = Val(mid(MyStr,Instr(MyStr,"-")+1))
end if

Range("B1") = Lower
Range("C1") = Upper



I like using the evaluate function with a sumproduct
MyNum = 22
Data = Evaluate("Sumproduct(--(B1:B100>=" & MyNum & ")," & _
"--(" & MyNum & "<=C1:C100),D1100)"

 

[ryguy7272]

Data > Text to Columns > Delimited (space and other, use -).
Now, copy transpose. Creats bins, as such:
1 < -- in cell B3
=b3+10 < -- fill down
....so let's say you go to 50, then
in cell C3
=FREQUENCY(A3:A13,B3:B7)
Select C3:C7
Hit Ctrl + Shift + Enter.

It may take a bit of work, with the parsing strings part, but once you take
care of that, you're home free.

HTH,
Ryan---

 

[Ron Rosenfeld]

Hi,

I have a data source that will be providing string values made up of
individual 1,2, or 3-digit numbers separated by spaces, ranges of
numbers indicated by a dash, or a mixture of both. There are no
repeat or overlapping values, and the numbers may range from 1 to 100.

For example, the string might look like this

"1 6-9 11 16-19 21 26-29"

The goal is to count how many of these numbers fall into one of ten
ranges of numbers, i.e. 1-10, 11-20, ... 91-100, get a lookup value
based on the range, and multiply that lookup value by the count of
numbers within the range.

My first thought was to parse the string into a single-dimension array
holding the individual numbers so I can later loop through the array
in order to get the lookup value, but would appreciate any suggestions
for making this more efficient.

Thanks very much,

Terry

Some thoughts on parsing the string and generating the count of each bin:

====================================
Option Explicit
Sub foo()
Const sInput As String = "1 6-9 11 16-19 21 24 26-29 30 31 39-43 89 90 91 100"
Dim aNumSrc As Variant
Dim aNumsTemp As Variant
Dim aNums() As Long
Dim aBins(0 To 9) As Long
Dim i As Long
Dim j As Long

ReDim aNums(0)

'split input string by spaces
aNumSrc = Split(sInput)
For i = 0 To UBound(aNumSrc)

'if the input is a range, then aNumsTemp will have
'2 elements; if not, it will only have one element
aNumsTemp = Split(aNumSrc(i), "-")
For j = aNumsTemp(0) To aNumsTemp(UBound(aNumsTemp))
aNums(UBound(aNums)) = j
ReDim Preserve aNums(UBound(aNums) + 1)
Next j
Next i

'remove last element which will be empty
ReDim Preserve aNums(UBound(aNums) - 1)

'get count of values in each range
'aBins(0) = 1 to 10
'...
'abins(9) = 91 to 100
For i = 0 To UBound(aNums)
j = (aNums(i) - 1) \ 10
aBins(j) = aBins(j) + 1
Next i

For i = 0 To 9
Debug.Print i * 10 + 1 & " to " & (i + 1) * 10, aBins(i)
Next i
End Sub
===============================
--ron

 

[TFTAJLLYMXZP]

Some thoughts on parsing the string and generating the count of each bin:

====================================
Option Explicit
Sub foo()
Const sInput As String = "1 6-9 11 16-19 21 24 26-29 30 31 39-43 89 90 91 100"
Dim aNumSrc As Variant
Dim aNumsTemp As Variant
Dim aNums() As Long
Dim aBins(0 To 9) As Long
Dim i As Long
Dim j As Long

ReDim aNums(0)

'split input string by spaces
aNumSrc = Split(sInput)
For i = 0 To UBound(aNumSrc)

    'if the input is a range, then aNumsTemp will have
    '2 elements; if not, it will only have one element
    aNumsTemp = Split(aNumSrc(i), "-")
    For j = aNumsTemp(0) To aNumsTemp(UBound(aNumsTemp))
        aNums(UBound(aNums)) = j
        ReDim Preserve aNums(UBound(aNums) + 1)
    Next j
Next i

'remove last element which will be empty
ReDim Preserve aNums(UBound(aNums) - 1)

'get count of values in each range
'aBins(0) = 1 to 10
'...
'abins(9) = 91 to 100
For i = 0 To UBound(aNums)
    j = (aNums(i) - 1) \ 10
    aBins(j) = aBins(j) + 1
Next i

For i = 0 To 9
    Debug.Print i * 10 + 1 & " to " & (i + 1) * 10, aBins(i)
Next i
End Sub
===============================
--ron

Ron,

There's several ideas in your sample code I can definitely make good
use of. Thanks a ton!

Terry

 

[TFTAJLLYMXZP]

Ron,

There's several ideas in your sample code I can definitely make good
use of.  Thanks a ton!

Terry

I take that back. This is a brilliant piece of coding. I can't
imagine how it could be made any more efficient than this. Wow!

 

[Ron Rosenfeld]

I take that back. This is a brilliant piece of coding. I can't
imagine how it could be made any more efficient than this. Wow!

Glad to help. It was fun to do.

Of course, if your Bins were not to be set up in multiples of 10, then the bin
counts would have to be generated in a different fashion.
--ron

 

[Ron Rosenfeld]

Glad to help. It was fun to do.

Of course, if your Bins were not to be set up in multiples of 10, then the bin
counts would have to be generated in a different fashion.
--ron

Also, it would be quicker (save a loop through aNums) to do the bin count while
generating the list of numbers. But probably less easily understood and
modifiable in the future.
--ron