Author: Oscar Cronquist Article last updated on December 19, 2018

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:

=LOOKUP(2, 1/(COUNTIFS($E$4:$E4, $B$5:$B$9, $F$4:$F4, $C$5:$C$9)=0), B$5:B$9)

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

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Count unique distinct values within same week, month or year

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

Calculate your stock portfolio performance with Net Asset Value based on units in excel

In a previous related post we calculated the stock portfolio performance using the most current stock prices compared to buying […]

Advanced custom date filter

Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]

Filter duplicates within same date, week or month

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

How to highlight unique distinct values

The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]

Prevent duplicate records in a worksheet

This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]