Highlight duplicates with same date, week or month
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, only subsequent duplicates are highlighted.
Conditional formatting formula:
Explaining Conditional formatting formula
Step 1 - Concatenate cell values
The cell references $C3 and $D3 are locked to each column, however, they change when CF move to the next row. The ampersand sign concatenates the values in these cells.
$C3&"-"&$D3
becomes
40184&"-"&1150
and returns
"40184-1150"
Step 2 - Concatenate values using expanding cell references
These cell references expand as the CF moves to cells below, the formula keeps track of previous values so it can detect duplicate values.
$C3:$C$3&"-"&$D3:$D$3
becomes
40184&"-"&1150
and returns
"40184-1150".
Step 3 - Compare concatenated values
The equal sign lets you compare values, the result is always TRUE or FALSE.
$C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3
becomes
"40184-1150" = "40184-1150"
and returns TRUE.
Step 4 - Convert boolean values
The SUMPRODUCT function can't work with boolean values, we must convert it (them) to their numerical equivalents. TRUE = 1 and FALSE equals 0 (zero).
--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3)
becomes
--(TRUE)
and returns 1.
Step 5 - Sum values
The SUMPRODUCT function sums the values in the array in this step.
SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))
becomes
SUMPRODUCT(1)
and returns 1.
Step 6 - Check if duplicate
This steps checks if the sum is larger than 1, if it is then TRUE is returned and the cell is highlighted.
SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))>1
becomes
1>1
and returns FALSE. Cell C3 is not highlighted.
Get Excel *.xlsx file
Highlight-duplicates-within-same-date-week-month-year.xlsx
Highlight duplicates on same week
Conditional formatting formula:
Highlight duplicates on same month
Conditional formatting formula:
Get excel sample file for this article.
Highlight-duplicates-within-same-date-week-month-year.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999
MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December)
WEEKNUM(serial_number, return_type)
Returns the week number in the year
Cf duplicates category
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]
Table of Contents How to highlight duplicate values Highlight the smallest duplicate number 1. How to highlight duplicate values The […]
Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]
This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered […]
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]
Question: I need to delete duplicates in two different columns together. How do I highlight the second or more duplicates […]
Conditional formatting category
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Dates category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Conditional Formatting categories
Excel categories
4 Responses to “Highlight duplicates with same date, week or month”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
HI
I need to learn excel both the basic and the advance level specifically through online which includes both audio and video.
Im working in a Corporate and feel excel will enhance my skills.
Can you contact me through my emial and let me know the feasibility and convenience.
Thanks
Nirmala
Nirmala,
I have sent you an email.
/Oscar
Hi,
I'm trying to find a way to highlight cells with text/number (column B) if reoccurred 3 times or more within the last 3 days (dates in column A)
I hope someone can help
Hello,
I'm doing this for a spreadsheet where I don't want a blank cell (in the second column) to match with another blank cell. How can I modify the formula accordingly? For instance, in my work I drag down the dates in column A before entering the rest of the data. I'm looking for a match between column A&E between rows. But at the moment it does this, but also highlights all the prepped rows at the end where I've dragged down the date. I want it not to consider the same date in A and a blank in E to be a match with another same date A and blank E.
Thanks!