Find the biggest gap between numbers in rows

[l33t]

Hey Guys,

I have an excel file with >12500 rows in one column. It contains such random strings with 20 digits (numbers from 1-62):

2,3,4,6,7,8,12,13,14,24,30,42,45,46,48,50,56,58,59,61
1,2,6,8,11,12,13,16,17,21,24,27,28,33,34,42,44,48,58,61
3,7,10,13,14,15,18,21,23,24,25,29,30,34,37,48,51,56,57,60
8,11,13,16,17,19,21,27,29,35,36,39,42,44,46,50,53,54,57,60
2,4,7,9,21,26,28,30,32,34,35,37,38,39,43,44,50,60,61,62
10,13,15,18,21,22,23,24,25,26,40,42,48,49,51,52,56,59,61,62
1,2,4,7,14,15,18,20,24,29,30,32,35,41,42,50,52,55,58,62
1,4,8,9,10,12,17,24,25,33,37,41,43,44,46,49,52,59,61,62
1,2,4,6,9,12,15,17,21,24,30,31,32,36,41,44,47,48,51,58
2,7,10,12,15,16,20,24,25,27,30,33,39,44,45,52,54,55,58,60
5,7,10,11,20,22,24,31,32,33,36,38,39,41,43,47,50,52,56,58
3,6,8,9,14,15,19,21,25,28,34,37,39,45,47,54,55,56,57,59
1,2,3,4,5,8,14,15,18,20,23,31,33,37,42,45,46,51,52,55

I need to know whats the biggest gap between rows where a number hasn't repeated. For example - I search for any number (e.g 59) and I need to know what's the largest gap between two rows (in whole document 12500 rows) where number 59 hasn't repeated. In this example it's 4 row gap between 59's.

Hope that I make myself clear.

Thanks for your answer in advance!

[oeldere]

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

[Tony Valko]

Try this...

Data Range

	A	B	C
1			Find
2	2,3,4,6,7,8,12,13,14,24,30,42,45,46,48,50,56,58,59,61		59
3	1,2,6,8,11,12,13,16,17,21,24,27,28,33,34,42,44,48,58,61		4
4	3,7,10,13,14,15,18,21,23,24,25,29,30,34,37,48,51,56,57,60	------	------
5	8,11,13,16,17,19,21,27,29,35,36,39,42,44,46,50,53,54,57,60
6	2,4,7,9,21,26,28,30,32,34,35,37,38,39,43,44,50,60,61,62
7	10,13,15,18,21,22,23,24,25,26,40,42,48,49,51,52,56,59,61,62
8	1,2,4,7,14,15,18,20,24,29,30,32,35,41,42,50,52,55,58,62
9	1,4,8,9,10,12,17,24,25,33,37,41,43,44,46,49,52,59,61,62
10	1,2,4,6,9,12,15,17,21,24,30,31,32,36,41,44,47,48,51,58
11	2,7,10,12,15,16,20,24,25,27,30,33,39,44,45,52,54,55,58,60
12	5,7,10,11,20,22,24,31,32,33,36,38,39,41,43,47,50,52,56,58
13	3,6,8,9,14,15,19,21,25,28,34,37,39,45,47,54,55,56,57,59
14	1,2,3,4,5,8,14,15,18,20,23,31,33,37,42,45,46,51,52,55

This array formula** entered in C3:

=MAX(FREQUENCY(IF(ISERROR(FIND(","&C2&",",","&A2:A14&",")),ROW(A2:A14)),IF(ISNUMBER(FIND(","&C2&",",","&A2:A14&",")),ROW(A2:A14))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This may be a bit slow to calculate on >12500 rows of data.

[Tony Valko]

Also, you may have to replace the commas in the formula with semi-colons depending on your regional settings.

[l33t]

String.xls
Here is a small part of the file.
Don't know how to formulate my question differently.
I need a formula, where you search any number and it finds the biggest gap in whole document between rows where number isn't repeated.

Thanks Tony Valko! Will take a look if it works!

[l33t]

It worked!
Thanks a lot, Tony Valko!
You're the man! thumbsup.gif

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.