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

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)"


"(E-Mail Removed)" wrote:

> 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
>

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''.


"(E-Mail Removed)" wrote:

> 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
>

On Sat, 11 Apr 2009 16:01:27 -0700 (PDT), (E-Mail Removed) wrote:

>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

On Apr 11, 5:51*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Sat, 11 Apr 2009 16:01:27 -0700 (PDT), TFTAJLLYM...@spammotel.com wrote:
> >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

Ron,

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

Terry

On Apr 11, 7:49*pm, TFTAJLLYM...@spammotel.com wrote:
> On Apr 11, 5:51*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
>
>
>
> > On Sat, 11 Apr 2009 16:01:27 -0700 (PDT), TFTAJLLYM...@spammotel.com wrote:
> > >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
>
> 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 Sat, 11 Apr 2009 20:09:18 -0700 (PDT), (E-Mail Removed) wrote:

>> 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

On Sun, 12 Apr 2009 06:56:39 -0400, Ron Rosenfeld <(E-Mail Removed)>
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