Highlighting instances where an instance of data in two adjacent columns in a row is duplicated in a different row

Question

I have a Small Excel table, which is liable to expand substantially over time, and each row of data is entered chronologically, in real time. Each row includes a serial number (column B) and an error code (column B). I need to identify occasions when the data in column 2 and the data in column 3 are repeated on later date, in a row whch might be WEEKS or months away from a previous occurrence.

Myself and a colleague, who are in no way good at Excel,but we acknowledge our limitations, have spent all day trying various things - including HOURS on Google. No joy so far

Answer 1

So here are a few options depending on what you are interested in:

Select your used range in columns B:C > New formatting rule > Based on formula

1) To highlight only rows that will occur at least one more time later on in your data:

=AND(COUNTIFS($B:$B,$B1,$C:$C,$C1)>1,COUNTIFS($B$1:B1,$B1,$C$1:C1,$C1)<COUNTIFS($B:$B,$B1,$C:$C,$C1))

2) To highlight only the first occurence of rows that will occur more often:

=AND(COUNTIFS($B:$B,$B1,$C:$C,$C1)>1,COUNTIFS($B$1:B1,$B1,$C$1:C1,$C1)=1)

3) To highlight all rows that have duplicates:

=COUNTIFS($B:$B,$B1,$C:$C,$C1)>1

4) To highlight all rows that have occured before:

=COUNTIFS($B$1:B1,$B1,$C$1:C1,$C1)>1

5) Highlight the last occurences of rows that have duplicates before them:

=AND(COUNTIFS($B$1:B1,$B1,$C$1:C1,$C1)>1,COUNTIFS($B$1:B1,$B1,$C$1:C1,$C1)>COUNTIFS($B:$B,$B1,$C:$C,$C1)-1)

I would recommend the 3rd or 4th option due to more calculation on the other ones. I highly recommend you to NOT apply these formulas to whole columns as they will slow down your sheet significantly. What is important to know is that once you add a row to your data Excel automatically should adjust the range to where the rule applies to, to include your new row!

A) Before adding new row of data:

B) After adding new row of data: