How best to parse string lists of numbers
TFTAJL...@spammotel.com
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
ryguy7272
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---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
joel
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),D1:D100)"
Ron Rosenfeld
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
TFTAJL...@spammotel.com
Ron,
There's several ideas in your sample code I can definitely make good
use of. Thanks a ton!
Terry
TFTAJL...@spammotel.com
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
>> 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!
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
wrote:
>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