Identify number of numeric sets in a long string of numbers

[ExcelMonkey]

I have a string of numbers which represent multiple sets. The string look as
follows below. I need a function which returns an array which outlines:

1) the number of sets of numbers
2) the min number per set
3) the max number per set

Based on the data below it would return a 2D array with three columns as
follows:

Set 1 - Min of 3, Max of 5
Set 2- Min of 2, Max of 5
Set 3- Min of 3, Max of 5


3
4
5
2
3
4
5
3
4
5

Any ideas on how to do this?

Thanks

EM

 

[Ron Coderre]

Yup....Try this:

With

A2:B4 containing the below list of range start/end values
Start End
10 15
21 23
30 32

D1: =MIN($A$2:$A$4)

D2:
=IF(MAX($D$1:$D1)=MAX($B$2:$B$4),"",IF(ISNA(MATCH(D1,$B$2:$B$4,0)),D1+1,INDEX($A$2:$A$4,MATCH(D1,$A$2:$A$4,1)+1)))
Copy that formula down as far as needed

In this example, the below list is returned in D112
10
11
12
13
14
15
21
22
23
30
31
32

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[paul.robinson]

I have a string of numbers which represent multiple sets. The string look as
follows below. I need a function which returns an array which outlines:

1) the number of sets of numbers
2) the min number per set
3) the max number per set

Based on the data below it would return a 2D array with three columns as
follows:

Set 1 - Min of 3, Max of 5
Set 2- Min of 2, Max of 5
Set 3- Min of 3, Max of 5

3
4
5
2
3
4
5
3
4
5

Any ideas on how to do this?

Thanks

EM

Hi
Your question and example are not at all clear. What is the "min
number per set" for example? Can you say exactly what you want from
your example string.
regards
Paul

 

[Bob Phillips]

I agree. What do you mean by a set?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Coderre]

Sorry, I thought I deleted that message when I realized it didn't work for
your situation. Evidently, I pressed Send by mistake.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Dave D-C]

This looks like a good programming class problem.
A start for me would be an outer loop of finding sets and an
inner loop of finding the Min(first) and Max(last).
Then filling the array (of integers? of cells?) with the data.

initialize outer loop (find sets)
do
inner loop goes here or
if (..) then exit do ' exit outer loop
or inner loop goes here
loop
end of function/sub routine

initialize inner loop (find min/max)
do
min/max here or
if (..) then exit do ' exit inner loop
or min/max here
loop
fill array with results

 

[ExcelMonkey]

Set means a run of numbers. So the firs set are the first three numbers 3,4,5
the second set is the 4th - 7th numbers 2,3,4,5 and the third set are the
last three numbers 3,4,5. Effectively, the string represents a collections
of number sets which may increment logically or may not. But each set can be
identified by a max and min value in each set. So the first set has three
numbers and the min is 3 (1st #)and the max is 5 (3rd #). The min for the
second set is 2 (4th #) and the max is 5 (7th#). The third set has a min of
3 (8th #) and a max of 5(10th #). So I need a function which pulls in the
string, breaks out each individual set using somthing like a Stip function
dropping each set into an array (not sure what delimeter would be used) and
then I can apply a min and max to these arrays

3
4
5
2
3
4
5
3
4
5

Thanks
EM

 

[Bernd P]

Hello,

If your input is in cells A1:A10 then select cells D1:F3, for example,
and array-enter (enter with CTRL + SHIFT + ENTER, not just ENTER):
=idsets(A1:A10)

The UDF idsets:
Function idsets(r As Range) As Variant
Dim v, vR(1 To 100, 1 To 3)
Dim i As Long, m As Long, b As Boolean
Dim lmin As Long, lmax As Long
m = 2147483647
b = False
For Each v In r
If v < m Then
'New set
If b Then
i = i + 1
vR(i, 1) = "Set " & i
vR(i, 2) = lmin
vR(i, 3) = lmax
Else
b = True
End If
m = v
lmin = m
lmax = m
Else
If v > lmax Then lmax = v
If v < lmin Then lmin = v
End If
m = v
Next v
i = i + 1
vR(i, 1) = "Set " & i
vR(i, 2) = lmin
vR(i, 3) = lmax
idsets = vR
End Function

Press ALT + F11, insert a new module and copy function text into new
module, finally switch back to worksheet and press F9.

Regards,
Bernd