• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting the number of consecutive identical numbers

C

clumpa

Member
I have a table with 18 columns, number of rows is variable. Each cell contains a number from 0 to 5. I would like to know the length of the longest string of the same non zero number. Where the longest strings are identical I would like to see the string for the higher number. Ideally I would like to see the length of the string and for which number.

I have no idea of how to tackle the problem, or even if it is possible!!
 
Hello,
Welcome to the forum.

Assuming that you are looking to find the similar digits for each row, the attached workbook shows one approach. I am sure a better approach will come to mind after I post this, but here is something to get you started!

Cheers,
Sajan.
 

Attachments

  • Chandoo-Count number of similar digit sequence.xlsx
    11.2 KB · Views: 60
Hi ,

The frequency of the numbers , provided the list of numbers is known , is straightforward ; in your case , you say that the numbers are from 0 through 5 , which makes the formulae much simpler.

The formula :

=MAX(FREQUENCY(row_of_numbers,{0,1,2,3,4,5})+ROW($1:$7)%)

will return a number such as 9.06 , where 9 is the number of occurrences , and 6 signifies the 6th element in the array :

{0,1,2,3,4,5}

which means the most frequently occurring number is 5.

The addition of ROW($1:$7)% is to take care of duplicates where you want the higher number to be returned.

We need to use ROW($1:$7)% instead of ROW($1:$6)% , because even though there are only 6 elements in the array of numbers , the FREQUENCY function adds an extra bin ( the 7th in this case ) , to take care of data items which do not fall in any of the given list of bins.

Narayan
 
NARAYANK991 said:
Hi ,

The frequency of the numbers , provided the list of numbers is known , is straightforward ; in your case , you say that the numbers are from 0 through 5 , which makes the formulae much simpler.

The formula :

=MAX(FREQUENCY(row_of_numbers,{0,1,2,3,4,5})+ROW($1:$7)%)

will return a number such as 9.06 , where 9 is the number of occurrences , and 6 signifies the 6th element in the array :

{0,1,2,3,4,5}

which means the most frequently occurring number is 5.

The addition of ROW($1:$7)% is to take care of duplicates where you want the higher number to be returned.

We need to use ROW($1:$7)% instead of ROW($1:$6)% , because even though there are only 6 elements in the array of numbers , the FREQUENCY function adds an extra bin ( the 7th in this case ) , to take care of data items which do not fall in any of the given list of bins.

Narayan
Click to expand...
Showing my ignorance, what is the significance of the { brackets and how do I define "row_of_numbers"?
Hopefully I will learn from the experts!
 
Hi ,

That was just an example ; instead of row_of_numbers , substitute the reference of any row range e.g. B2:S2 which will be 18 columns.

The brackets { and } are used to define static arrays ; since your numbers of interest are 0 through 5 , you can either enter these numbers in any worksheet cells , say N1:N6 , and use the reference N1:N6 in your formula , or you can directly specify the numbers within the formula by using the brackets { and }.

Suppose you do go ahead with the above changes , then the formula in any unused cell will be as follows :

=MAX(FREQUENCY($B2:$S2,$N$1:$N$6)+ROW($1:$7)%)

entered as an array formula , using CTRL SHIFT ENTER.

You can copy this formula down as far as your data extends.

Narayan
 
NARAYANK991 said:
Hi ,

That was just an example ; instead of row_of_numbers , substitute the reference of any row range e.g. B2:S2 which will be 18 columns.

The brackets { and } are used to define static arrays ; since your numbers of interest are 0 through 5 , you can either enter these numbers in any worksheet cells , say N1:N6 , and use the reference N1:N6 in your formula , or you can directly specify the numbers within the formula by using the brackets { and }.

Suppose you do go ahead with the above changes , then the formula in any unused cell will be as follows :

=MAX(FREQUENCY($B2:$S2,$N$1:$N$6)+ROW($1:$7)%)

entered as an array formula , using CTRL SHIFT ENTER.

You can copy this formula down as far as your data extends.

Narayan
Click to expand...
Hi
Thanks for the prompt response. What the formula is doing for me is returning the highest number of occurrences of the highest number. What I am looking for is length of the longest string of identical numbers and if the length of strings are equal then show that for the highest number
e.g. 222333344444003334
Your current formula would return, for me, 7.04 where I want to see 5.04, using the same format for the result, which is unimportant as I would just want to know that 4 occurred in a string 5 times consecutively
 
Hi ,

That's my mistake , sorry. Have you checked Sajan's uploaded workbook ? I have not yet checked it , but you should find a solution there.

Narayan
 
Sajan said:
Hello,
Welcome to the forum.

Assuming that you are looking to find the similar digits for each row, the attached workbook shows one approach. I am sure a better approach will come to mind after I post this, but here is something to get you started!

Cheers,
Sajan.
Click to expand...
Hi
Thanks, it does what I want. When I finally thought to expand the formula box I could see all of the formula!
By taking the MAX value of the 5 results cells, which also takes care of 2 or more numbers (1 to 5) having equal number of sequences and by using ROUNDDOWN I can display the longest sequence for which number.
I will wait to see if a "better" approach emerges
 
What I need to do to extend Sajan's formular to include values from 1 through 9? I am not a expert in excel. Any help would be greatly appreciated.

Amin
 
Hi Amin,

Welcome to the forum. In the future, you should start a new thread, rather than adding to an existing one (especially one that's older). However, the answer to your question would be to change the array formula to:
=LARGE(CHOOSE({1;2;3;4;5;6;7;8;9},
MAX(FREQUENCY(IF($A2:$S2=1, COLUMN($A2:$S2)), IF($A2:$S2<>1, COLUMN($A2:$S2)))+0.1),
MAX(FREQUENCY(IF($A2:$S2=2, COLUMN($A2:$S2)), IF($A2:$S2<>2, COLUMN($A2:$S2)))+0.2),
MAX(FREQUENCY(IF($A2:$S2=3, COLUMN($A2:$S2)), IF($A2:$S2<>3, COLUMN($A2:$S2)))+0.3),
MAX(FREQUENCY(IF($A2:$S2=4, COLUMN($A2:$S2)), IF($A2:$S2<>4, COLUMN($A2:$S2)))+0.4),
MAX(FREQUENCY(IF($A2:$S2=5, COLUMN($A2:$S2)), IF($A2:$S2<>5, COLUMN($A2:$S2)))+0.5),
MAX(FREQUENCY(IF($A2:$S2=6, COLUMN($A2:$S2)), IF($A2:$S2<>6, COLUMN($A2:$S2)))+0.6),
MAX(FREQUENCY(IF($A2:$S2=7, COLUMN($A2:$S2)), IF($A2:$S2<>7, COLUMN($A2:$S2)))+0.7),
MAX(FREQUENCY(IF($A2:$S2=8, COLUMN($A2:$S2)), IF($A2:$S2<>8, COLUMN($A2:$S2)))+0.8),
MAX(FREQUENCY(IF($A2:$S2=9, COLUMN($A2:$S2)), IF($A2:$S2<>9, COLUMN($A2:$S2)))+0.9)),
COLUMN(A1))

Remember to confirm using Ctrl+Shift+Enter, not just Enter.
 
Luke M said:
Hi Amin,

Welcome to the forum. In the future, you should start a new thread, rather than adding to an existing one (especially one that's older). However, the answer to your question would be to change the array formula to:
=LARGE(CHOOSE({1;2;3;4;5;6;7;8;9},
MAX(FREQUENCY(IF($A2:$S2=1, COLUMN($A2:$S2)), IF($A2:$S2<>1, COLUMN($A2:$S2)))+0.1),
MAX(FREQUENCY(IF($A2:$S2=2, COLUMN($A2:$S2)), IF($A2:$S2<>2, COLUMN($A2:$S2)))+0.2),
MAX(FREQUENCY(IF($A2:$S2=3, COLUMN($A2:$S2)), IF($A2:$S2<>3, COLUMN($A2:$S2)))+0.3),
MAX(FREQUENCY(IF($A2:$S2=4, COLUMN($A2:$S2)), IF($A2:$S2<>4, COLUMN($A2:$S2)))+0.4),
MAX(FREQUENCY(IF($A2:$S2=5, COLUMN($A2:$S2)), IF($A2:$S2<>5, COLUMN($A2:$S2)))+0.5),
MAX(FREQUENCY(IF($A2:$S2=6, COLUMN($A2:$S2)), IF($A2:$S2<>6, COLUMN($A2:$S2)))+0.6),
MAX(FREQUENCY(IF($A2:$S2=7, COLUMN($A2:$S2)), IF($A2:$S2<>7, COLUMN($A2:$S2)))+0.7),
MAX(FREQUENCY(IF($A2:$S2=8, COLUMN($A2:$S2)), IF($A2:$S2<>8, COLUMN($A2:$S2)))+0.8),
MAX(FREQUENCY(IF($A2:$S2=9, COLUMN($A2:$S2)), IF($A2:$S2<>9, COLUMN($A2:$S2)))+0.9)),
COLUMN(A1))

Remember to confirm using Ctrl+Shift+Enter, not just Enter.
Click to expand...
Thank you very much. This formula is a life saver.
 
You must log in or register to reply here.
Back
Top