Highlight Duplicates within Same Month in Google Sheets

Have you ever found yourself in a situation where you need to quickly identify duplicate entries within the same month in Google Sheets? Well, today I’m going to let you in on a little secret. With a simple custom formula, you can effortlessly highlight duplicates that fall within the same month in Google Sheets.

Imagine this scenario: you’re managing your inventory, and you want to keep track of items that you have procured more than once in the same month. By highlighting these duplicates, you can easily identify any patterns or discrepancies in your purchasing habits.

Now, you might be thinking, “Doesn’t Google Sheets already have built-in rules for highlighting duplicates based on dates?” And you would be correct. However, there are no rules specifically designed to highlight duplicates within the same month in conditional formatting in Google Sheets. But fear not, my friend, because I’m about to show you the solution.

Highlight Duplicates within the Same Month – Two Columns

Let’s start with a simple example. Take a look at the dataset in the screenshot below.

In this example, we have a two-column dataset. The conditional formatting formula is used to highlight duplicate items (column B) based on the month (column A). The first instance is excluded from the highlight.

For instance, in the month of August, we purchased Gravel 5-10 mm twice and White Sand twice. With the custom formula, the duplicates within the same month, i.e., rows 7, 9, 11, and 12, are highlighted.

Format Rule and Applying It

To apply the custom rule, follow these simple steps:

1. Select the range A2:B or any range mentioned earlier.
2. Head into Format > Conditional formatting.
3. Under Format Rules, select “Custom Formula Is” from the drop-down.
4. Copy the custom format rule =ArrayFormula(countifs($B$2:$B2,$B2,eomonth($A$2:$A2,0),eomonth($A2,0),row($B$2:$B2),"<="&row($B2)))>1 and paste it into the blank field.
5. Optionally, customize the formatting style.
6. Select Done.

That’s it! You have successfully applied the custom rule to highlight duplicates within the same month in Google Sheets.

The Logic Behind Highlighting Duplicates within the Same Month

Now, let’s dive into the logic behind the custom formula. The formula is based on the COUNTIFS function, a powerful tool in Google Sheets. To understand it better, follow these steps:

1. Copy and paste the custom formula in cell C2.
2. Edit the formula to remove the >1 in the last part.
3. Drag down the fill handle (the small blue box at the bottom-right corner of cell C2) until you reach the last row.
4. If the formula returns >1 in any cell, it means that those records fall under duplicates within the same month category.

This logic is the foundation of the custom formula. By highlighting the rows where the formula returns >1, you can easily identify the duplicate items that fall within the same month. The >1 acts as a logical test, returning TRUE for duplicates and FALSE for single instances. Google Sheets Conditional Formatting then highlights the TRUE rows.

If you’re interested in getting a running count of duplicates, simply remove the eomonth($A$2:$A2,0),eomonth($A2,0), parts and >1 from the formula.

The Rule for More than Two Columns

But what if you have a more complex dataset with additional columns? Don’t worry; I’ve got you covered. Let’s assume we have added a quantity column and want to include that as a criterion within the format rule.

In this case, the custom formula should be modified to consider the items and their quantities. Take a look at the screenshot below for clarification.

In this example, we have included the quantity column as a criterion. The duplicates are highlighted based on the item, the month, and the quantity.

To include additional columns, follow the same technique mentioned earlier.

And there you have it! Now you know how to highlight duplicates that fall within the same month in Google Sheets. I hope this little secret helps you better manage your data and streamline your processes. If you have any further questions or need assistance, don’t hesitate to reach out. Enjoy exploring the possibilities with Google Sheets!

Thanks for joining me on this journey. If you’d like to learn more tips, tricks, and secrets about Google Sheets, be sure to check out Crawlan.com. It’s a treasure trove of knowledge for all your Google Sheets needs.