I have some data like this:
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.
So if your solution requires such input, it is not a problem.
Here is a sample excel data: Book1.xlsx
thanks for your help!
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!