## Filter duplicates within same date, week or month in excel

*Article updated on December 02, 2010*

### Filter duplicates on same date

Array formula in F3:

Copy cell and paste it down as far as needed.

Array formula in G3:

Copy cell and paste it down as far as needed.

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

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]Extract a list of duplicates from three columns combined using array formula in excel

Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array […]Filter unique distinct and duplicate values from a large data set in excel 2007

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]### 2 Responses to “Filter duplicates within same date, week or month in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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!