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.
Download 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:
Download 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
Highlight duplicates in a filtered Excel Table
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Highlight overlapping date ranges using conditional formatting
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
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 […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
List all unique events in a month
Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]
Count unique distinct values within same week, month or year
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
Remove duplicates within same month or year
The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]
2 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