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:

=INDEX(Date, SMALL(IF(MATCH(YEAR(Date)&"-"&MONTH(Date)&"-"&Item, YEAR(Date)&"-"&MONTH(Date)&"-"&Item, 0)=ROW(Date)-MIN(ROW(Date))+1, ROW(Date)-MIN(ROW(Date))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER.

Copy cell and paste it down as far as needed.

Array formula in C15:

=INDEX(Item, SMALL(IF(MATCH(YEAR(Date)&"-"&MONTH(Date)&"-"&Item, YEAR(Date)&"-"&MONTH(Date)&"-"&Item, 0)=ROW(Date)-MIN(ROW(Date))+1, ROW(Date)-MIN(ROW(Date))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER.

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)

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.

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

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)