# Count consecutive same text starting from the latest date going backwards

1. ## Count consecutive same text starting from the latest date going backwards

Hi,

I've been struggling in automating to count consecutive same text starting from the latest date going backwards. Attached here is a sample excel file. Does anyone have an idea on how to execute this? Thanks!

2. ## Re: Count consecutive same text starting from the latest date going backwards

There's probably a better way to do this, but here's one solution

=12-MAX(IF(C2:K2=LOOKUP(2,1/(C2:K2<>L2),C2:K2),COLUMN(C2:K2)))
Array formula, use Ctrl-Shift-Enter

LOOKUP(2,1/(C2:K2<>L2) find the last occurrence in a row of the value which is NOT equal to L2 the last value in the row.
The rest of the formula finds the maximum column number where that value exists.
The result is subtracted from 12 (Column L is the 12th column in the sheet) to give the final value.

3. ## Re: Count consecutive same text starting from the latest date going backwards

Hi Special-K,

Thank you for your answer. Will there be a way of doing this without using array?

4. ## Re: Count consecutive same text starting from the latest date going backwards

Typically when you need to compare part of a data set against all other parts of a data set (1 compared to 2, 1 compared to 3, 1 compared to 4, etc) then you need to use an array formula as this is effectively what an array allows for, performing the same calculation on each element of your data set instead of on the data set as a whole. Specifically in your case, to know if a number is consecutive we need to compare it against other values to discover if it is.

So the short answer is, there is unlikely to be a non array style formula capable of what you asked for in this case

5. ## Re: Count consecutive same text starting from the latest date going backwards

Thank you Zer0Cool for the clarification. Appreciate it.

6. ## Re: Count consecutive same text starting from the latest date going backwards

Non-CSE alternative:

=12-AGGREGATE(14,6,(C2:K2=LOOKUP(2,1/(C2:K2<>L2),C2:K2))*COLUMN(C2:K2),1)

7. ## Re: Count consecutive same text starting from the latest date going backwards

Another way:

=12-LOOKUP(12,COLUMN(C2:L2)/(C2:L2<>L2))

8. ## Re: Count consecutive same text starting from the latest date going backwards

You could use AGGREGATE but you're using Excel 2007 so AGGREGATE won't work.
I'll have another think...

9. ## Re: Count consecutive same text starting from the latest date going backwards

Thank you Phuocam and 63falcondude. The non-CSE alternatives solved it for me.

10. ## Re: Count consecutive same text starting from the latest date going backwards

Happy to help. Thanks for the rep! I just converted the formula from post #2.

If AGGREGATE worked for you, please update your profile to show the current version of Excel that you have.

11. ## Re: Count consecutive same text starting from the latest date going backwards

Special-K, the aggregate solved it for me. The excel version I used is from Office 365, forgot update it. Anyways, thank you for your time.

12. ## Re: Count consecutive same text starting from the latest date going backwards

63falcondude I've updated it already. Thank you all for the inputs. Really appreciate it. This is solved.

There are currently 1 users browsing this thread. (0 members and 1 guests)