Remove duplicates on same date in excel
Question:
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.
Answer:
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:
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
Related posts:
Remove duplicates within same month or year in excel
Remove duplicates in same week in excel
Remove duplicates and sort dates by each row in excel
Recommended categories
[...] in Excel on Apr.13, 2010. Email This article to a Friend In a previous post we created a unique distinct list of dates and data removing any duplicates on same [...]