Checking equality with previous data

[Mgc26133]

Hello,

I am trying to come up with a formula for Conditional Formatting.

I have a data of
Column A: Plate Numbers
Column B: Number of how many times they've moved, and
Column C: the reason for movement.

I've attached an example file.

If you look at the 12th row, you will see CRT240's 3rd movement is "Repair". If the same car's previous movement (2nd in this case) is also "Repair", I need C12 highlighted.

I only need to check "Repair". Other movements are irrelevant.

Formatting will be applied to entire column, so the formula needs to be as short and clean as possible.

Thanks in advance!

[Bo_Ry]

Please try Conditional Formatting applies to C2:Cxx

=SEARCH("repair*repair",LOOKUP(2,1/($A$1:$A1=$A2),$C$1:$C1)&C2)

[Mgc26133]

I can't even begin to understand the logic behind the formula, but it works! Thanks so much

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Ochimus]

Mgc26133,

Try the attached.

Rather than needing the whole Column, Col C is a 'dynamic' range (MOVEMENT) so the CF is applied only to rows with data, and expands automatically as you add new entries.

Cols K and L are also "dynamic" ranges, listing the Unique values in Cols A and B. They fill the DropLists in E2 and F2. So if you add a new vehicle to Col K. or another Category of work to Col L, they are added automatically in the DropLists.

Select a vehicle from E2 and the Work from F2. If there is a second instance, the cell in Col C fills.

CF formula is as follows:
=IF(A2=E$2,COUNTIFS($A$2:$A2,E$2,$C$2:$C2,$F$2))=2

(For each row in Col A with data, if the registration is what you selected in E2, count how many records up to that row are for that vehicle where the work is whatever you selected in F2. If this is the second instance, fill the cell)
If you want to look for a different frequency at any time (e.g. a third instance), just change the number at the end from =2 to =(whatever).

Ochimus

[Mgc26133]

Mgc26133,

Try the attached.

Rather than needing the whole Column, Col C is a 'dynamic' range (MOVEMENT) so the CF is applied only to rows with data, and expands automatically as you add new entries.

Cols K and L are also "dynamic" ranges, listing the Unique values in Cols A and B. They fill the DropLists in E2 and F2. So if you add a new vehicle to Col K. or another Category of work to Col L, they are added automatically in the DropLists.

Select a vehicle from E2 and the Work from F2. If there is a second instance, the cell in Col C fills.

CF formula is as follows:
=IF(A2=E$2,COUNTIFS($A$2:$A2,E$2,$C$2:$C2,$F$2))=2

(For each row in Col A with data, if the registration is what you selected in E2, count how many records up to that row are for that vehicle where the work is whatever you selected in F2. If this is the second instance, fill the cell)
If you want to look for a different frequency at any time (e.g. a third instance), just change the number at the end from =2 to =(whatever).

Ochimus

It seems that it would require me to check each plate number individually. That is not practical, the original list has thousands of unique plate numbers and other datas, full data currently nearing 100k rows and more being added every day.