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 […]
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 […]
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?