Column A1 Has dates Column B as data
A1 : 1/1/2010 : 5000
A2 : 2/1/2010 : 4000
A3 : 1/1/2010 : 5000
A4 : 5/1/2010 : 5000
A5 : 6/1/2010 : 4000
From column B the values which are duplicate i want to remove only those which are on same date .
Remove Duplicates from Column B by comparing from Column A Which has date. as, if the duplication is occurred on next date it should not be counted as not a duplicate because the same data is entered in the next date.
Example : Customer has Purchased Item A on 1/1/2010
Customer has purchase Item A on 1/1/2010
I want to assume that On 1/1/2010 Item A has a duplicate
If the customer has purchased Item A on 2/1/2010 . this is not duplicate because the item is purchased on the next date.
Array formula in D2:
Array formula in E2:
Download excel sample file for this article.
remove dupes on same date.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
Returns the relative position of an item in an array that matches a specified value