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

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

Formula in E3:

=LOOKUP(2, 1/((COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0)*(COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1)), B$3:B$11)

Copy cell and paste it to cell range E3:F5.

Explaining formula in cell E3

Step 1 - Keep track of previous values

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions. If a date and corresponding item already has been displayed the function returns 1. The cell references grow when the cell is copied to cells below, the formula keeps track of previously shown values.

COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0

becomes

COUNTIFS("Duplicates on same date",{40184; 40184; 40184; 40184; 40184; 40189; 40189; 40189; 40189},0,{1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131})=0

becomes

{0;0;0;0;0;0;0;0;0}=0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 2 - Find duplicates

COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1

becomes

COUNTIFS({40184; 40184; 40184; 40184; 40184; 40189; 40189; 40189; 40189},{40184; 40184; 40184; 40184; 40184; 40189; 40189; 40189; 40189},{1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131},{1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131})>1

becomes

{2;2;2;2;1;1;1;2;2}>1

and returns