Extract duplicate values with exceptions

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want in your duplicates list.

For example, column B contains values, column D contains exceptions. The formula is in cell F3, it extracts all duplicates from column B except values specified in column D.

The formula in cell E2 returns duplicate values from column A but values in column C are excluded from the list.

1. Extract duplicate values with exceptions

This example demonstrates a formula for earlier versions, Excel 2019, and previous versions. I recommend the smaller formula demonstrated in section 2 if you use Excel 365.

Array formula in cell F3:

Back to top

1.1 How to enter an array formula

1. Copy above array formula.
2. Double press on cell F3.
3. Paste formula to cell F3 (CTRL + v).
4. Press and hold CTRL + SHIFT simultaneously.
5. Press Enter once.
6. Release all keys.

The formula now begins with and ends with a curly bracket, this is Excel letting you know the formula is an array formula.

Don't enter these characters yourself, they appear automatically.

Back to top

1.2 Explaining the formula in cell F2

Step 1 - Count values

The COUNTIF function calculates the number of cells that is equal to a condition.

Function syntax: COUNTIF(range, criteria)

COUNTIF($B$3:$B$21, $B$3:$B$21)

becomes

COUNTIF({"Federer, Roger ";"Djokovic, Novak "; ... ;"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak "; ... ;"Almagro, Nicolas "})

and returns

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

Step 2 - Check if the count number is larger than 1

The larger than sign is a logical operator, it returns TRUE or FALSE.

COUNTIF($B$3:$B$21, $B$3:$B$21)>1

becomes

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

and returns

{TRUE; FALSE; FALSE; ... ; FALSE}.

Step 3 - Check if previous results are not in $B$3:$B$21

$F$2:F2 is a cell reference to the cell above F3, it grows when the cell is copied to cells below. This makes it possible to keep track of previous results.

COUNTIF($F$2:F2, $B$3:$B$21)=0

becomes

COUNTIF("Duplicates",{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "})=0

becomes

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

and returns

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

Step 4 - Count excluded values in $B$3:$B$21

COUNTIF($D$3:$D$4, $B$3:$B$21)<>1

becomes

COUNTIF({"Federer, Roger ";"Gonzalez, Fernando "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "})<>1

becomes

{1; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0}<>1

and returns

{FALSE;TRUE;TRUE; ... ;TRUE}

Step 5 - Multiply arrays - AND logic

The asterisk character lets you multiply numbers and boolean values in an Excel formula.

AND logic means that both conditions must be met, in other words, both arrays must contain TRUE in order to return TRUE.

TRUE * TRUE = TRUE (1)
TRUE * FALSE = FALSE (0)
FALSE * TRUE = FALSE (0)
FALSE * FALSE = FALSE (0)

When you multiply boolean values TRUE or FALSE the result is their numerical equivalent:
TRUE = 1
FALSE = 0 (zero)

(COUNTIF($B$3:$B$21, $B$3:$B$21)>1)*(COUNTIF($F$2:F2, $B$3:$B$21)=0)*(COUNTIF($D$3:$D$4, $B$3:$B$21)<>1)

becomes

{TRUE; FALSE; FALSE; ... ; FALSE}* {TRUE; TRUE; TRUE;  ... ; TRUE} * {FALSE;TRUE;TRUE; ... ;TRUE}

and returns

{0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0}

Step 6 - Divide 1 by the array

The division character lets you divide numbers and boolean values in an Excel formula. Some numbers in the array above are 0 (zero) and some are 1. You can't divide a number by 0 (zero), Excel returns an error value.

However, the LOOKUP function ignores errors which we can take advantage of in this formula.

1/((COUNTIF($B$3:$B$21, $B$3:$B$21)>1)*(COUNTIF($F$2:F2, $B$3:$B$21)=0)*(COUNTIF($D$3:$D$4, $B$3:$B$21)<>1))

becomes

1/{0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0}

and returns

{#DIV/0!; #DIV/0!; #DIV/0!; 1; ... ; #DIV/0!}.

Step 7 - Return value

The LOOKUP function find a value in a cell range and return a corresponding value on the same row.

Function syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP(2, 1/((COUNTIF($B$3:$B$21, $B$3:$B$21)>1)*(COUNTIF($F$2:F2, $B$3:$B$21)=0)*(COUNTIF($D$3:$D$4, $B$3:$B$21)<>1)), $B$3:$B$21)

becomes

LOOKUP(2, {#DIV/0!; #DIV/0!; #DIV/0!; 1; ... ; #DIV/0!}, {"Federer, Roger ";"Djokovic, Novak "; ... ;"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak "; ... ;"Almagro, Nicolas "})

and returns

"Wawrinka, Stanislas ".

Step 8 - Replace error values

The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

The formula returns error values when it runs out of values, the IFERROR function displays nothing when that happens.

IFERROR(LOOKUP(2, 1/((COUNTIF($B$3:$B$21, $B$3:$B$21)>1)*(COUNTIF($F$2:F2, $B$3:$B$21)=0)*(COUNTIF($D$3:$D$4, $B$3:$B$21)<>1)), $B$3:$B$21), "")

Back to top

2. Extract duplicate values with exceptions - Excel 365

The following formula is a dynamic array formula and is entered as a regular formula, it works only in Excel 365.

Formula in cell F3:

Back to top

2.1 Explaining array formula in cell F2

Step 1 - Check if the value is a duplicate

The COUNTIF function counts values based on a condition. It can also be used to count multiple values.

COUNTIF(B3:B21, B3:B21)>1

becomes

COUNTIF({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "})>1

becomes

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

The larger than character checks if the numbers in the array is larger than 1. The output is TRUE or FALSE.

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

returns

{TRUE; FALSE; FALSE; ... ; FALSE}.

Step 2 - Check if the value is in the exceptions list

This step returns an array that shows if the values are in the exceptions list or not.

COUNTIF(D3:D4, B3:B21)=0

becomes

COUNTIF({"Federer, Roger ";"Gonzalez, Fernando "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "})=0

and returns

{FALSE; TRUE; TRUE; ... ; TRUE}.

Step 3 - Apply AND logic

The asterisk lets you multiply the arrays. It returns TRUE only if both arrays contain TRUE.

TRUE * TRUE = TRUE (1)
FALSE* TRUE = FALSE (0)
TRUE * FALSE= FALSE (0)
FALSE * FALSE= FALSE (0)

Note that the boolean values are converted into their numerical equivalents, TRUE = 1, and FALSE = 0 (zero).

(COUNTIF(B3:B21,B3:B21)>1)*(COUNTIF(D3:D4,B3:B21)=0)

becomes

{TRUE; FALSE; FALSE; ... ; FALSE} * {FALSE; TRUE; TRUE; ... ; TRUE}

and returns

{0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0}

Step 4 - Filter values

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:B21,(COUNTIF(B3:B21,B3:B21)>1)*(COUNTIF(D3:D4,B3:B21)=0))

becomes

FILTER(B3:B21, {0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0})

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; ... ; "Almagro, Nicolas "}, {0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0})

and returns

{"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}.

Step 5 - Extract unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(B3:B21,(COUNTIF(B3:B21,B3:B21)>1)*(COUNTIF(D3:D4,B3:B21)=0)))

becomes

UNIQUE({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "})

and returns

{"Davydenko, Nikolay "; "Wawrinka, Stanislas "}.

Step 6 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

UNIQUE(FILTER(B3:B21,(COUNTIF(B3:B21,B3:B21)>1)*(COUNTIF(D3:D4,B3:B21)=0)))

x - B3:B21

LET(x,B3:B21,UNIQUE(FILTER(x,(COUNTIF(x,x)>1)*(COUNTIF(D3:D4,x)=0))))

Back to top

Duplicate values category

More than 1300 Excel formulas

Excel categories

Comments (3)