How to Highlight Conditional Duplicates in Google Sheets

Conditional Duplicates Based on IF Test in Another Column

The IF logical test is the key to highlighting conditional duplicates in Google Sheets. Let’s walk through the process step by step.

First, let’s assume we have a dataset with three products (A, B, and C) available at discounted prices. However, due to a shortage of items A and B, we are allowed to buy either one. If you buy both or either of them more than once, those items will be considered duplicates.

To apply the conditional formatting rule, follow these steps:

Select the range of cells where you want to apply the formatting.

Go to Format > Conditional formatting to open the sidebar panel.

Under “Single color,” enter the range of cells in the “Apply to range” field.

Select “Custom formula” below “Format rules” and insert the following formula:

=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))

Click Done and voila! The conditional duplicates in your dataset will now be highlighted.

Solution

Let’s break down the formula to understand how it works. Pay attention to the cell references in the formula:

$B$2:$B becomes IF($B$2:$B=”Book A”,”Book B”,$B$2:$B).

$B2 becomes IF($B2=”Book A”,”Book B”,$B2).

The modified 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))

By applying this formula, you can customize the formatting to highlight specific duplicates based on your requirements.

But wait, there’s more! What if you only want to highlight the rows containing Books A and B and ignore other rows? In that case, you can use an OR test in conditional formatting. Simply replace $A2<>”” with OR($B2=”Book A”,$B2=”Book B”) in the formula.

With this trick up your sleeve, you’ll have full control over your conditional duplicate highlighting in Google Sheets.

Enjoy a clutter-free and organized dataset, thanks to this simple yet effective technique. Say goodbye to manual searching and hello to efficient data management!

That’s all about highlighting conditional duplicates in Google Sheets. If you found this article helpful, visit Crawlan.com for more insightful tips and tricks.

Thanks for reading, and happy Googling!