Track column value change

Hello

I am trying to work out a way to track the number of times column value changes.

I have a single column of colours and would like to track every time it changes with values of 1 and 2. For example, colours red, blue, green, yellow & brown returning 1,2,1,2,1 etc. Please refer to the attached spread sheet. I tried the IF statement but can’t get it right.

Could someone help please?

Thanks
Hoss

Hi Ace_XL

Thank you for your reply. I applied your formula but it doesn't seem to work unless I am doing it wrong?!! Please see attached.

Could you please let me know how to get it right?

Thanks
Hoss

Hi popipipo

Thank you for your reply. Your formula does not seem to work either. I get 1,2,2,2,2,2,1,2,2,2,2,2 instead of 1,2,2,2,1,1,2,1,1,1,1,1. Please let me know if I am doing something wrong?!

Thanks
Hossein

Hi popipipo

Thank you for your reply. Your formula does not seem to work either. I get 1,2,2,2,2,2,1,2,2,2,2,2 instead of 1,2,2,2,1,1,2,1,1,1,1,1. Please let me know if I am doing something wrong?!

Thanks
Hossein

Hello popipipo

Your second formula is working fine thanks and I don't mind that it is not short.

Thanks
Hoss

Hi Ace_XL

I am still getting wrong result but I have now got a working formula from popipipo.

Thanks again
Hoss