I have excel sheet with list of phone numbers within 1 column in a given series range. The objective is to identify and rank the numbers relative to frequency of occurence of consecutive digits. Any of the digits; 0,1,2,3,4,5,6,7,8,9 should be evaluated irrespective of position. Where multiple digit share the max frequency of consecutive occurenes, all digits with consequtive occurences are listed separated by commas and in order of their position from left to right.Where no consecutive numbers are identified, shoud return NULL. Anyone has an idea of how to achieve this?
Calculating frequency of occurence of consecutive digits in list of phone numbers
- Thread starter anonmaz
- Start date
Excel Wizard
Active Member
Please try at
E3 for Digit
=IF(F3="NULL","NULL",MID(TEXT(NPV(-0.9,IF(F3=MOD(SMALL(9-LEN(SUBSTITUTE(B3,REPT({0,1,2,3,4,5,6,7,8,9},{2;3;4;5;6;7;8;9}),))+10^{1,2,3,4,5,6,7,8,9,10},8*{1,2,3,4,5,6,7,8,9,10}),10),{0,1,2,3,4,5,6,7,8,9}/10)),REPT("\,0",SUM(--(F3=MOD(SMALL(9-LEN(SUBSTITUTE(B3,REPT({0,1,2,3,4,5,6,7,8,9},{2;3;4;5;6;7;8;9}),))+10^{1,2,3,4,5,6,7,8,9,10},8*{1,2,3,4,5,6,7,8,9,10}),10))))),2,9))
F3 for Max OCCURRENCE
=IFERROR(1/(1/MAX(MOD(SMALL(9-LEN(SUBSTITUTE(B3,REPT({0,1,2,3,4,5,6,7,8,9},{2;3;4;5;6;7;8;9}),))+10^{1,2,3,4,5,6,7,8,9,10},8*{1,2,3,4,5,6,7,8,9,10}),10))),"NULL")
E3 for Digit
=IF(F3="NULL","NULL",MID(TEXT(NPV(-0.9,IF(F3=MOD(SMALL(9-LEN(SUBSTITUTE(B3,REPT({0,1,2,3,4,5,6,7,8,9},{2;3;4;5;6;7;8;9}),))+10^{1,2,3,4,5,6,7,8,9,10},8*{1,2,3,4,5,6,7,8,9,10}),10),{0,1,2,3,4,5,6,7,8,9}/10)),REPT("\,0",SUM(--(F3=MOD(SMALL(9-LEN(SUBSTITUTE(B3,REPT({0,1,2,3,4,5,6,7,8,9},{2;3;4;5;6;7;8;9}),))+10^{1,2,3,4,5,6,7,8,9,10},8*{1,2,3,4,5,6,7,8,9,10}),10))))),2,9))
F3 for Max OCCURRENCE
=IFERROR(1/(1/MAX(MOD(SMALL(9-LEN(SUBSTITUTE(B3,REPT({0,1,2,3,4,5,6,7,8,9},{2;3;4;5;6;7;8;9}),))+10^{1,2,3,4,5,6,7,8,9,10},8*{1,2,3,4,5,6,7,8,9,10}),10))),"NULL")