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!

 

[braindiesel]

I already have... The above are not photos... you can copy and paste the sheets shared in my posts plus the formulas and where to put them are there

 

[BrSuthe]

ok, as response to your previous post, you said " In cells A2:C2, you set your low (1) high (15)" .. what is 1 and 15? the size of rows are fixed. there are only 20 data for each row from A:T.

" So if you wanted to know how many between 10-15 in the 3rd row you could get that number "
I need the smallest range for each row, and the count of data in this range. the smallest range may start from the beginning of the row, or it can be in the middle or at the end.
the formula should be flexible to calculate this.

I hope i made it clear. thanks.

 

[braindiesel]

I think I have answered all of this....If you want a count of the values from 20-30 then put 20 in the Low and 30 in the high and the formula looks at that specific range and counts the values.
This should factor in wherever they are in the range, beginning middle end...
Edit: I added Conditional formatting to show what is being counted

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:U7	Expression	=AND(B5>=$B$2,B5<=$C$2)	text	NO

 

[BrSuthe]

I see. but what i need is to calculate the smallest (dynamic) range in each row. so if i pick the first and the last number of each row, your formula gives 20 for each time.
it is not the range i asked. I gave an example to another poster, i think it is a simple example. I can't choose numbers, it is fixed, the first to the last value. and the smallest range with the highest data will be dynamic naturally. I mean it can be at the beginning, middle or end of each row. the formula or vba needs to figure this out. that's what i am asking.

 

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

This is the example i mentioned.

but this example could be like this:
1................5..................15......20.......25.26...28.30.31.32.34.35..............56..........75..... etc

in this case the smallest range with the highest number of data would be 25-35.
it is in the middle of the row.

 

[braindiesel]

Book3
	B	C	D	E	F	G
5	1	1	2	5	5	9
6	2	2	4	4	6	9
7	1	4	5	5	9	9
Sheet1

Lets simplify...
If the predetermined target is 1-5
First shown row... Are you looking for...
5 = counting values 1,1,2,5,5
B5:F5 = the range of said example
3 = Counting unique occurances 1.2.5
C5:E5 = the range of said example

Or something else entirely.

And finallywhere do you get the 1-5 range from if not from a cell reference?

 

[braindiesel]

This is the example i mentioned.

but this example could be like this:
1................5..................15......20.......25.26...28.30.31.32.34.35..............56..........75..... etc

in this case the smallest range with the highest number of data would be 25-35.
it is in the middle of the row.

So smallest range of highest cluster... Is there a range size?
Because 30-35 is the smallest range of the highest number of data

 

[BrSuthe]

not like that. it is not predetermined 1-5.
the row has 20 data, from A:T. it is fixed, it doesn't change.
I make examples without calculations, just brain work, not much thinking. no calculations or formulas. I don't have formulas for this.

 

[BrSuthe]

So smallest range of highest cluster... Is there a range size?
Because 30-35 is the smallest range of the highest number of data

It is up to the formula (if it can be done), the most feasible or logical range. I picked these examples randomly.

 

[braindiesel]

I am happy to help, but formulas are boring and predictable... judgement doesn't apply... calculations do...
Not sure how to write a formula that looks at abunch of data without something fixed... like cluster of 10 or cluster if no gaps or cluster if no gaps >5...
You need something fixed to write the formula I believe... figure that out and I will be happy to try and solve it.
It is hard to write a formula based on personal judgement