## 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.

### Download 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.

### Download 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

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

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

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

Extract a list of duplicates from a column

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Extract a list of duplicates from three columns combined

The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is […]

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]

Extract duplicate values with exceptions

The formula in cell E2 returns duplicate values from column A but values in column C are excluded from the […]

### 2 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.

**Contact Oscar**

You can contact me through this contact form

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!