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.
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---
-- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''.
> 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.
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)"
> 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.
>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
> >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
Ron,
There's several ideas in your sample code I can definitely make good use of. Thanks a ton!
> > >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
> 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!
On Sun, 12 Apr 2009 06:56:39 -0400, Ron Rosenfeld <ronrosenf...@nospam.org> 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
