Filter duplicates within same date, week or month
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date.
Formula in E3:
Copy cell and paste it to cell range E3:F5.
Explaining formula in cell E3
Step 1 - Keep track of previous values
The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions. If a date and corresponding item already has been displayed the function returns 1. The cell references grow when the cell is copied to cells below, the formula keeps track of previously shown values.
COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0
becomes
COUNTIFS("Duplicates on same date",{40184; 40184; 40184; 40184; 40184; 40189; 40189; 40189; 40189},0,{1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131})=0
becomes
{0;0;0;0;0;0;0;0;0}=0
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
Step 2 - Find duplicates
COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1
becomes
COUNTIFS({40184; 40184; 40184; 40184; 40184; 40189; 40189; 40189; 40189},{40184; 40184; 40184; 40184; 40184; 40189; 40189; 40189; 40189},{1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131},{1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131})>1
becomes
{2;2;2;2;1;1;1;2;2}>1
and returns
{TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}
Step 3 - Multiply arrays
We use AND logic because both conditions must be met.
(COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0)*(COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1)
becomes
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}
and returns
{1;1;1;1;0;0;0;1;1}
Step 4 - Divide 1 with array
The LOOKUP function ignores error values and this is what we are going to use. In order to get an error if a value in the array is FALSE or 0 (zero) we divide 1 with 0 and excel returns !DIV/0 error.
1/((COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0)*(COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1))
becomes
1/{1;1;1;1;0;0;0;1;1}
and returns
{1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;1;1}
Step 5 - Get value
The LOOKUP function returns a value on the corresponding row if it is not an error value, simplified.
LOOKUP(2, 1/((COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0)*(COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1)), B$3:B$11)
becomes
LOOKUP(2, {1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;1;1}, B$3:B$11)
becomes
LOOKUP(2, {1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;1;1}, {40184; 40184; 40184; 40184; 40184; 40189; 40189; 40189; 40189})
and returns 40189 (1/11/2010) in cell E3.
Get Excel *.xlsx file
Filter-duplicates-within-same-date-week-month-year.xlsx
Filter duplicates within same week
Formula in B16:
Array formula in F16:
Copy cell and paste it down as far as needed.
Array formula in G16:
Copy cell and paste it down as far as needed.
Filter duplicates within same month
Array formula in F29:
Copy cell and paste it down as far as needed.
Array formula in G29:
Copy cell and paste it down as far as needed.
Get excel sample file for this article.
Filter-duplicates-within-same-date-week-month-year.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
SMALL(array,k) returns the k-th smallest row number in this data set.
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
ROW(reference) returns the rownumber of a reference
MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value
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
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 […]
Duplicate values category
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]
The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]
Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
3 Responses to “Filter duplicates within 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.
Oscar,
for the Filter duplicates within same date, week or month in excel
Using the Pic above, if I put instead the a value of 1150 in cell D5, then the formula does not give you the desired result.
Instead of the , I used the = and it works fine.
Formula in G3
=INDEX($D$3:$D$11, SMALL(IF(MATCH($C$3:$C$11&$D$3:$D$11, $C$3:$C$11&$D$3:$D$11, 0)=ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, "") , ROW(A1)))
Thanks for your great tutorials
The array formula creates a list of all duplicates. If I change cell D5 to 1150, Date 6-Jan-2010 now have two duplicate values. That is also the result of the array formula.
I am sure I can create an array formula that only show one duplicate value, later.
Thanks for commenting!
Trying to mod the forumla to list 3+ entries the same instead of two duplicates... any suggestions would be amazing?