Highlight Duplicate Values Based on Occurrence Days in Google Sheets

Have you ever wondered how to highlight duplicate values in Google Sheets based on their occurrence days? Well, look no further! In this tutorial, we will explore a unique approach to achieving this using conditional formatting. Forget about the basic tutorials you’ve come across before; this one is different!

Prerequisite (Sorting)

Before we dive into the conditional format rule, we need to ensure that our table is sorted correctly. Follow these steps to sort your table in Google Sheets:

1. Select the range A1:B, where column A contains sold dates and column B contains book titles.
2. Go to Data > Sort range > Advanced range sorting options.
3. Check the box that says “Data has header rows.”
4. Sort by Title – A -> Z.
5. Click on “Add another sort column.”
6. Sort by Date – A -> Z.
7. Click on Sort.

Now that our table is sorted, let’s move on to the exciting part – coding the conditional format rule!

Highlight UNIQUE Duplicate Values Based on Occurrence Days Less Than 30

In this approach, we want to highlight only the unique duplicate values based on their occurrence days. We don’t want any clutter, so once a book is highlighted, it won’t be highlighted again. Here’s the code for achieving this:

=and(len(A2), len(B2), A2=sortn(filter({filter($A$2:$A,$B$2:$B=B2);"x"}, {filter($A$2:$A,$B$2:$B=B2);"x"}- {"x";filter($A$2:$A,$B$2:$B=B2)}<31)))

I know the formula might look overwhelming at first, but fear not! Let’s break it down step by step:

1. len(A2) – Checks if cell A2 is not blank.
2. len(B2) – Checks if cell B2 is not blank.
3. A2=sortn(filter({filter($A$2:$A,$B$2:$B=B2);"x"}, {filter($A$2:$A,$B$2:$B=B2);"x"}- {"x";filter($A$2:$A,$B$2:$B=B2)}<31))) – Checks if the value in cell A2 is sold within 30 days of the last sale.

There are four filters and a sort function in the formula. They work together to filter and sort the dates based on occurrence days. The sortn function limits the output to one cell, which is the date we want to highlight. The formula then tests whether the value in A2 matches the highlighted date. The logical testing happens in each row, so the formula references A2, B2, A3, B3, and so on.

How to Change Duplicate Entry Range from within 30 Days to 7 Days?

If you want to highlight duplicate values based on a different range, say within 7 days, you can modify the formula accordingly. Simply change <31 to <8 in the formula, and it will highlight duplicates based on seven days’ occurrence.

Highlight ALL Duplicate Values Based on Occurrence Days Less Than 30

In this approach, we want to highlight all duplicate values based on their occurrence days, regardless of uniqueness. Here’s the formula for achieving this:

=and(len(A2), len(B2), regexmatch(row(A2)&"", "^"&textjoin("$|^",true,filter({filter(row($A$2:$A),$B$2:$B=B2);"x"}, {filter($A$2:$A,$B$2:$B=B2);"x"}- {"x";filter($A$2:$A,$B$2:$B=B2)}<31)) &"$"))

In this formula, we have made a few changes compared to the previous one:

1. Removed the sortn function to highlight all duplicates, not just one.
2. Instead of filtering dates, we now use the ROW function to filter rows.
3. The output is not the highlighted dates, but the corresponding row numbers.
4. The REGEXMATCH function matches the row numbers and highlights the cells accordingly.

And voila! Now you know how to highlight duplicate values based on their occurrence days in Google Sheets. It’s a powerful technique that can save you valuable time and effort.

Thanks for reading, and if you want more juicy secrets about Google Sheets and other exciting topics, don’t forget to visit Crawlan.com. Stay tuned for more amazing content!

Resources

• Highlight Duplicates in Single, Multiple Columns, All Cells in Google Sheets
• Highlight Partial Matching Duplicates in Google Sheets
• Skip Duplicates in Min | Small Value Highlighting Row Wise in Google Sheets
• Highlight Visible Duplicates in Google Sheets
• Highlight Max Value Leaving Duplicates in Row Wise in Google Sheets