Author: Oscar Cronquist Article last updated on October 08, 2018

In a previous post we created a unique distinct list of dates and data removing any duplicates on same date.

In this post we will remove duplicates within same week.

Array formula in B15:

=INDEX($B$4:$B$12, SMALL(IF(MATCH(YEAR($A$4:$A$12)&"-"&$A$4:$A$12&"-"&$C$4:$C$12, YEAR($A$4:$A$12)&"-"&$A$4:$A$12&"-"&$C$4:$C$12, 0)=ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1, ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER

Download excel sample file for this article.

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

Functions in this article:

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.

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

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

WEEKNUM(serial_number, return_type)
Returns the week number in the year