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.

Download Excel *.xlsx file

remove dupes on same date.xlsx

Related articles