Remove duplicates based on date
Question:
Column B has dates Column C as data
B5 : 1/1/2010 : 5000
B6 : 2/1/2010 : 4000
B7 : 1/1/2010 : 5000
B8 : 5/1/2010 : 5000
B9 : 6/1/2010 : 4000
From column C the values which are duplicate i want to remove only those which are on same date .
Remove Duplicates from Column C by comparing from Column B 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 E5:
Explaining formula in cell E5
Step 1 - Prevent duplicate records
The COUNTIFS function counts the number of cells across multiple ranges that equals all given conditions.
Every other argument in the COUNTIFS function contains an expanding cell reference, when the cell is copied to cells below the cell references grows accordingly. This makes the formula aware of previously shown values which prevents duplicates from showing up.
COUNTIFS($E$4:$E4, $B$5:$B$9, $F$4:$F4, $C$5:$C$9)=0
becomes
COUNTIFS("Date", {40179; 40180; 40179; 40183; 40184}, "Amount", {5000; 4000; 5000; 5000; 4000})=0
and returns
{0;0;0;0;0}.
A zero indicates that the record has not yet been displayed.
{0;0;0;0;0}=0
returns {TRUE; TRUE; TRUE; TRUE; TRUE}.
Step 2 - Divide 1 with array
If the array contains an error the LOOKUP function will ignore it which is great. To create an error for values that are FALSE (equivalent to 0 zero) simply divide 1 with the array.
1/(COUNTIFS($E$4:$E4, $B$5:$B$9, $F$4:$F4, $C$5:$C$9)=0)
becomes
1/{TRUE; TRUE; TRUE; TRUE; TRUE}
and returns
{1;1;1;1;1}.
Step 3 - Get value
LOOKUP(2, 1/(COUNTIFS($E$4:$E4, $B$5:$B$9, $F$4:$F4, $C$5:$C$9)=0), B$5:B$9)
becomes
LOOKUP(2, {1;1;1;1;1}, B$5:B$9)
becomes
LOOKUP(2, {1;1;1;1;1}, {40179; 40180; 40179; 40183; 40184})
and returns 40184 (1/6/2010) in cell E5.
Get Excel *.xlsx file
remove dupes on same date.xlsx
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Excel categories
3 Responses to “Remove duplicates based on date”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[...] 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 [...]
I have 4K rows - does it break if there are too many rows - it seems to stop functioning for me.
Jackie,
I recommend using formulas as described in the post if you need a "dynamic" calculation like in a dashboard or similar.
Follow instructions below if you want to simply remove duplicate records.
Select cell range A1:B5.
Go to tab "Data" on the ribbon.
Press with left mouse button on "Remove Duplicates" button
Press with left mouse button on OK
Press with left mouse button on OK button