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

### 3 Responses to “Filter duplicates within same date, week or month”

### Leave a Reply to Chris

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

Trying to mod the forumla to list 3+ entries the same instead of two duplicates... any suggestions would be amazing?