In this post, we can learn to highlight conditional duplicates in Google Sheets.

In this context, duplicates are not determined merely based on the multiple occurrences of rows/records. Then?

We will do an IF logical test in one of the columns to determine it. I’ll explain it first.

Assume we have three products (A, B, and C) available to buy at discounted prices.

But due to the shortage of items A and B, we are allowed to buy either one.

If you buy both of them or either of them more than once, such items will be considered duplicates.

Please see rows 4 and 6 w.r.t. Jesse for conditional duplicates. Needless to say, rows 9 and 10 are naturally duplicates.

The above is an example of highlighting conditional duplicates (based on IF test in another column) in Google Sheets.

Let’s code the formula rule.

Conditional Duplicates Based on IF Test in Another Column

The IF logical test is the essence of highlighting conditional duplicates in Google Sheets.

I’ve already provided highlight rules for duplicates. So I’m not explaining that here.

For the sample data, please refer to A2:B in the screenshot above.

We can use the following formula to highlight duplicates in A2:A without IF test in another column (B2:B).

=and($A2<>"",ArrayFormula(countif($A$2:$A&$B$2:$B,$A2&$B2)>1))

It will highlight the records in rows 9 and 10 because they are the only duplicate rows in the dataset.

We want to consider Book A and B as the same.

So that, if a person buys both of them, that will also be considered as multiple occurrences of records.

Solution

We should pay attention to the cell (range) references $B$2:$B and $B2 in the formula.

Let’s apply the IF function with them. What we will do here is we will replace “Book A” with “Book B” like;

1. $B$2:$B becomes if($B$2:$B="Book A","Book B",$B$2:$B).
2. $B2 becomes if($B2="Book A","Book B",$B2).

So the formula to highlight conditional duplicates in Google Sheets is as follows.

=and($A2<>"",ArrayFormula(COUNTIF($A$2:$A&if($B$2:$B="Book A","Book B",$B$2:$B),$A2&if($B2="Book A","Book B",$B2))>1))

To apply, do as follows.

1. Select Format > Conditional formatting to open the sidebar panel.
2. Under “Single color,” enter A2:A in the field below “Apply to range.”
3. Select “Custom formula” below “Format rules” and insert the above formula.
4. Select Done and voila!

What if I just want to highlight the rows containing Books A and B and ignore other rows?

In that scenario, we may require to use an OR test in condition formatting.

Just replace $A2<>"" with OR($B2="Book A",$B2="Book B").

The formula will ignore the purchase of Books by Mike even though the corresponding records occur twice.

That’s all about highlighting conditional duplicates in Google Sheets.

Thanks for the stay. Enjoy!

You May Also Like: Highlight Duplicate Values Based on Occurrence Days in Google Sheets.