The smallest range with the highest number of data

[BrSuthe]

I have some data like this:

								DATA
1	1	2	5	5	9	11	14	14	18	18	29	29	30	37	38	40	40	61	62
2	2	4	4	6	9	9	10	12	15	16	17	30	31	35	40	41	45	50	60
1	4	5	5	9	9	10	10	11	25	25	28	30	35	45	50	51	57	60	68

The values are up to 100. No bigger values. Each row has 20 columns of data. No value repeats more than twice.
I need to find the smallest range with the high number of data in this range.

For example, for the first row, I calculated it by hand. my solution is like this:
Row1 : Range 1-18, and the number of data : 11

If it is easier, I can join all data in a single cell with textjoin.

DATA (alternative single cell version)

1,1,2,5,5,9,11,14,14,18,18,29,29,30,37,38,40,40,61,62

2,2,4,4,6,9,9,10,12,15,16,17,30,31,35,40,41,45,50,60

1,4,5,5,9,9,10,10,11,25,25,28,30,35,45,50,51,57,60,68

So if your solution requires such input, it is not a problem.

Here is a sample excel data: Book1.xlsx

thanks for your help!

 

[BrSuthe]

Thanks for your help. I appreciate it.

 

[Eric W]

If you could quantify your requirements in a rule, we can do something. Basically it sounds like you want the most numbers in the smallest range. So if we set our rule to be: Find the maximum value of S/r(i,j), where S is the number of numbers in the i-j range, and r(i,j) = the range size (j-i). Compute that for all values of i,j from 1 to 100. Once you find the maximum value, return the i and j values.

This sounds a bit tricky for a formula, so I wrote it up as a UDF. To try it, open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. In the window that opens, paste this code:

Function GetRange(r1 As Range, minrng As Long)
Dim ar1(1 To 100) As Long, i As Long, j As Long
Dim pct As Double, si As Long, sj As Long, wpct As Double

    For Each c In r1
        ar1(c.Value) = ar1(c.Value) + 1
    Next c
    
    pct = -1
    For i = 1 To 99
        For j = i + minrng To 100
            c = 0
            For k = i To j
                c = c + ar1(k)
            Next k
            If c > 0 Then
                wpct = CDbl(c) / (CDbl(j) - CDbl(i))
                If wpct > pct Then
                    pct = wpct
                    si = i
                    sj = j
                End If
            End If
        Next j
    Next i
    
    GetRange = si & "/" & sj
 
End Function

Press Alt-Q to close the editor. Then you can use the function like so:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1									DATA
2	1	1	2	5	5	9	11	14	14	18	18	29	29	30	37	38	40	40	61
3	2	2	4	4	6	9	9	10	12	15	16	17	30	31	35	40	41	45	50
4	1	4	5	5	9	9	10	10	11	25	25	28	30	35	45	50	51	57	60
5	1	5	15	20	25	26	28	30	31	32	34	35	56	76	80	83	88	90	91
6
7
8	1/11
9	2/12
10	1/11
11	25/35
Sheet11

Cell Formulas
Range	Formula
A8:A11	A8	=getrange(A2:S2,10)

The first parameter in the function is the range, the second is the minimum size of the range you want. I originally did not have the size parameter, and the results were always a 1 or 2 number range, depending on duplicates. As you see by these test cases, the "best" answer always turns out to be the minimum size you specify.

For the first example, it returns 1-11, but you say you want 1-18. If you want to get that instead, you're going to have to come up with a way of showing your preference for 1-18, by weighting count vs. range perhaps.

 

[braindiesel]

I decided to give it a final kick...
Change cell A1 to a 6 for a cluster of 6. 3 for a cluster of 3... I believe it will work well with clusters of 3 - 10

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Variance in Cluster of 5									DATA
2	4	1	1	2	5	5	9	11	14	14	18	18	29	29	30	37	38	40	40	61	62	4	8	9	9	9	9	7	15	15	12	19	9	11
3	4	2	2	4	4	6	9	9	10	12	15	16	17	30	31	35	40	41	45	50	60	4	7	5	6	6	6	7	7	18	16	19	23	11
4	2	1	4	5	5	9	9	10	10	11	25	25	28	30	35	45	50	51	57	60	68	8	5	5	5	2	16	15	18	19	10	20	22	21
Sheet1

Cell Formulas
Range	Formula
V2:AH4	V2	=IF(COLUMNS($V$2:V2)<=(COLUMNS($B$2:$U$2)+1)-$A$1,OFFSET(B2,0,$A$1-1)-B2,"")
A2:A4	A2	=OFFSET(U2,0,LARGE((V2:AH2>0)*(V2:AH2=MIN(V2:AH2))*COLUMN(V2:AH2),1)-COLUMNS(B2:U2)-1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:U4	Expression	=AND(COLUMN(B2)>=LARGE(($V2:$AH2>0)*($V2:$AH2=MIN($V2:$AH2))*COLUMN($V2:$AH2),1)-COLUMNS($B$2:$U$2)-1+1,COLUMN(B2)<=LARGE(($V2:$AH2>0)*($V2:$AH2=MIN($V2:$AH2))*COLUMN($V2:$AH2),1)-COLUMNS($B$2:$U$2)-1+$A$1)	text	NO

 

[BrSuthe]

If you could quantify your requirements in a rule, we can do something. Basically it sounds like you want the most numbers in the smallest range. So if we set our rule to be: Find the maximum value of S/r(i,j), where S is the number of numbers in the i-j range, and r(i,j) = the range size (j-i). Compute that for all values of i,j from 1 to 100. Once you find the maximum value, return the i and j values.

This sounds a bit tricky for a formula, so I wrote it up as a UDF. To try it, open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. In the window that opens, paste this code:

Function GetRange(r1 As Range, minrng As Long)
Dim ar1(1 To 100) As Long, i As Long, j As Long
Dim pct As Double, si As Long, sj As Long, wpct As Double

    For Each c In r1
        ar1(c.Value) = ar1(c.Value) + 1
    Next c
   
    pct = -1
    For i = 1 To 99
        For j = i + minrng To 100
            c = 0
            For k = i To j
                c = c + ar1(k)
            Next k
            If c > 0 Then
                wpct = CDbl(c) / (CDbl(j) - CDbl(i))
                If wpct > pct Then
                    pct = wpct
                    si = i
                    sj = j
                End If
            End If
        Next j
    Next i
   
    GetRange = si & "/" & sj

End Function

Press Alt-Q to close the editor. Then you can use the function like so:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1									DATA
2	1	1	2	5	5	9	11	14	14	18	18	29	29	30	37	38	40	40	61
3	2	2	4	4	6	9	9	10	12	15	16	17	30	31	35	40	41	45	50
4	1	4	5	5	9	9	10	10	11	25	25	28	30	35	45	50	51	57	60
5	1	5	15	20	25	26	28	30	31	32	34	35	56	76	80	83	88	90	91
6
7
8	1/11
9	2/12
10	1/11
11	25/35
Sheet11

Cell Formulas
Range	Formula
A8:A11	A8	=getrange(A2:S2,10)

The first parameter in the function is the range, the second is the minimum size of the range you want. I originally did not have the size parameter, and the results were always a 1 or 2 number range, depending on duplicates. As you see by these test cases, the "best" answer always turns out to be the minimum size you specify.

For the first example, it returns 1-11, but you say you want 1-18. If you want to get that instead, you're going to have to come up with a way of showing your preference for 1-18, by weighting count vs. range perhaps.

Thanks a lot, Eric. This may be the closest solution I can get in Excel probably.