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!

 

[eduzs]

I didn't understand:
You want the range of values? so why on Row 1 is not 1-62?
What did you mean by number of data 11?

 

[BrSuthe]

yes, I need the smallest range of values with the highest number of data from the list given. So it should be the peak where values are gathered in the range.

There are 11 values in this range (1 1 2 5 5 9 11 14 14 18 18).
so between 1 and 18 there are 11 numbers available.

 

[eduzs]

I can't see the logic yet, sorry. Altought I can't open files here.
Why not 1-62?
2nd row 2-60
3rd row 1-68
...

 

[BrSuthe]

Lets exemplify it like this:

if this is our list of numbers:

1 2 3 4 5 6 7 ... 9 ... 10 .........................................................40..................45.................50............53.... 58.......... etc...

and if we wanted to give the smallest range with highest numbers here, we would say 1- 10, right? not 9- 58 or 40 - 58, etc..

I don't need the whole list as the smallest range. it is the data, and i need to select the smallest range from the data.

Note: I didn't include repeating numbers in the above example. A value may repeat sometimes but not never more than twice.

 

[braindiesel]

Are you looking for a single Targeted row... like this?

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Start	End	Row	Starting Column	Ending Column	Total Count
2	1	15	1	1	9	9
3
4									DATA
5	1	1	2	5	5	9	11	14	14	18	18	29	29	30	37	38	40	40	61	62
6	2	2	4	4	6	9	9	10	12	15	16	17	30	31	35	40	41	45	50	60
7	1	4	5	5	9	9	10	10	11	25	25	28	30	35	45	50	51	57	60	68
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SMALL((OFFSET(A4,C2,0,1,20)>(A2-1))*COLUMN(OFFSET(A4,C2,0,1,20)),1+COUNTIF(OFFSET(A4,C2,0,1,20),"<"&A2))
E2	E2	=LARGE((OFFSET(A4,C2,0,1,20)<(B2+1))*COLUMN(OFFSET(A4,C2,0,1,20)),1)
F2	F2	=E2-D2+1

 

[braindiesel]

Or did you want it by row?
EDIT:D1:D2 can be deleted... irrelevant in this one

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Start	End	Row
2		1	15	1
3
4	Total Occurances									DATA
5	9	1	1	2	5	5	9	11	14	14	18	18	29	29	30	37	38	40	40	61	62
6	10	2	2	4	4	6	9	9	10	12	15	16	17	30	31	35	40	41	45	50	60
7	9	1	4	5	5	9	9	10	10	11	25	25	28	30	35	45	50	51	57	60	68
Sheet1

Cell Formulas
Range	Formula
A5:A7	A5	=LARGE((B5:T5<($C$2+1))*COLUMN(B5:T5),1)-SMALL((B5:T5>($B$2-1))*COLUMN(B5:T5),1+COUNTIF(B5:T5,"<"&$B$2))+1

 

[BrSuthe]

your formula works on row of data, right? I don't understand your post, sorry.
each row from A1:T1 is a set of data. A2:T2 is a second set of data.

 

[braindiesel]

your formula works on row of data, right? I don't understand your post, sorry.
each row from A1:T1 is a set of data. A2:T2 is a second set of data.

My first example was the "Choose your own adventure" model...
In cells A2:C2, you set your low (1) high (15) and which row you want data from... So if you wanted to know how many between 10-15 in the 3rd row you could get that number

My second example sounds like what you wanted... the count of occurances by row... My formula calculates the total by row for each row, based on the high and low you set in cells A2, B2

 

[BrSuthe]

is it possible to share excel file? pls. I'm trying to understand.