Table of contents

1. Remove duplicates within same month and year in excel
2. Remove duplicates within same year in excel

Remove duplicates within same month and year in excel

Array formula in B15:

Copy cell and paste it down as far as needed.

Array formula in C15:

Copy cell and paste it down as far as needed.

Named ranges

Date (B4:B12)
Item (C4:C12)
What is named ranges?

Dynamic named ranges

If you are adding values continuously you can create a dynamic named range.

1. Click tab "Formulas"
2. Click "Name Manager"
3. Click "New.."
4. Type a name (Date)
5. Formula in "Refers to:" =OFFSET(Sheet1!$B$4, 0, 0, COUNT(IF(Sheet1!$B$4:$B$1000="", "", 1)), 1)
6. Type a name (Item)
7. Formula in "Refers to:" =OFFSET(Sheet1!$C$4, 0, 0, COUNT(IF(Sheet1!$C$4:$C$1000="", "", 1)), 1)

Download excel sample file for this article.

Remove-duplicates-in-same-month.xls
(Excel 97-2003 Workbook *.xls)

Remove duplicates within same year in excel

Array formula in B38:

=INDEX($B$27:$B$35, SMALL(IF(MATCH(YEAR($B$27:$B$35)&"-"&$C$27:$C$35, YEAR($B$27:$B$35)&"-"&$C$27:$C$35, 0)=ROW($B$27:$B$35)-MIN(ROW($B$27:$B$35))+1, ROW($B$27:$B$35)-MIN(ROW($B$27:$B$35))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed.

Array formula in C38:

=INDEX($C$27:$C$35, SMALL(IF(MATCH(YEAR($B$27:$B$35)&"-"&$C$27:$C$35, YEAR($B$27:$B$35)&"-"&$C$27:$C$35, 0)=ROW($B$27:$B$35)-MIN(ROW($B$27:$B$35))+1, ROW($B$27:$B$35)-MIN(ROW($B$27:$B$35))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed.

Download excel sample file for this article.

Remove-duplicates-in-same-month.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)