Hi All,
I have a simple question. I need to find a way to calculate the highest number of times a cell is repeated with a particular word.
i.e.
Column A
[1] 1 Book
[2] 2 Book
[3] 1 Book
[4] 2 Book
[5] Reserved
[6] 1 Book
[7] 2 Book
[8] Reserved
[9] Reserved
[10] 2 Book
[11] 2 Book
[12] Reserved
[13] Reserved
[14] Reserved
Simply I would like to query column a for the word "Reserved" and return a value where it was repeated the highest number of times consecutively.
As you can see rows 12 to 14, it was repeated 3 times. I would like that value "3" to be entered into a particular cell. Same goes if it was 4 or more times repeated in Column A.
Thanks.
j0nd0e
Hello,
A simple solution. I use Excel 365 and following formula is put in a helper column (B) to do the counting. Also need to add an empty extra row on top, so the data starts from row 2. Just copy the formula down as needed.
=(B1+--(A2="Reserved"))*--(A2="Reserved")
You then just use the =MAX(B:B) formula in choosen cell to get the highest number.
Br,
Anders
Thank you Anders, that worked perfectly!
