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.
Download Excel *.xlsx file
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Vlookup – Return multiple unique distinct values
Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]
Extract a unique distinct list sorted from A to Z
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Extract a unique distinct list from three columns
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Extract a unique distinct list and ignore blanks
Question: How do I extract a unique distinct list from a column containing blanks? Answer: Cell range B3:B12 contains several blank […]
Extract unique distinct values from a multi-column cell range
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
Extract unique distinct values A to Z from a range and ignore blanks
This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]
How to create a unique distinct list based on two conditions
Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]
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.
Click "Remove Duplicates" button
Click OK
Click OK button