Max consectutive values in a block of columns and rows

Hello again

I have a range of data which is 9 columns wide with rows which keep getting added to. I need a function that will report the most consecutive occurences of a particular value. There are only two values in the data entry A or B, but the consecutive nature of the values runs accross the rows. Also, the data may be blank which should just be ignored.

So for example:

A A A B B A
A A B A B B A A A
A A A B A A B
A B B B B A A B A

The answer should report A=6 and B=4

Thanks again

Craig

I could get you consecutive values in each row and a MAX of that, however if MAX consecutive values are across rows (as for 'B') its a bit of a struggle. Can you have your data rearranged to a single row or column?

See attached

Easily done with a little help from
http://chandoo.org/forums/topic/tran...nto-one-column

See attached. Does this work for you?

In case there are blanks at the end (or in between) rows, the single column will display a 0 as a corresponding value.

The current array formula that determines the max number of consecutives ignores zeros (and hence blanks from the original data too!), so a "AAA A" sequence would be read as 4 consecutive A's

If you want the blanks to be considered as breaking the consecutiveness and "AAA A" to be read as 3 consecutive A's only, change the array formulas to

Please Login or Register  to view this content.

Does the attached work for you?

I havent checked the results though