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 formula in cell F2

Step 1 -

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

Step 2 -

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

Step 3 -

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

Step 4 -

(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)

Step 5 -

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))

Step 6 -

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)

Step 7 -

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 ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Federer, Roger ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Federer, Roger ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"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; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; 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)

becomes

COUNTIF({"Federer, Roger ";"Gonzalez, Fernando "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Federer, Roger ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})

and returns {1; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0}.

Step 3 - Convert True to False

The NOT function converts True to False and False to True. The numerical equivalent to TRUE is 1 and False is 0 (zero).

NOT(COUNTIF(D3:D4, B3:B21))

becomes

NOT({1; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0})

and returns {FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 4 - 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 convertted into their numerical equivalents, TRUE = 1, and FALSE = 0 (zero).

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

becomes

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE} * {FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; 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 5 - Filter values

The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category and is only available to Excel 365 subscribers.

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

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 "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Federer, Roger "; "Davydenko, Nikolay "; "Verdasco, Fernando "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Federer, Roger "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "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 6 - Save formula to value

The LET function assigns names to calculation results, this can reduce a formula significantly.

LET(z, FILTER(B3:B21, (COUNTIF(B3:B21, B3:B21)>1)*NOT(COUNTIF(D3:D4, B3:B21))), formula)

FILTER(B3:B21, (COUNTIF(B3:B21, B3:B21)>1)*NOT(COUNTIF(D3:D4, B3:B21))) is assigned to z.

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

Step 7 - Evaluate formula in LET function

FILTER(z, MATCH(z, z, 0)<>SEQUENCE(ROWS(z)))

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, MATCH({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, 0)<>SEQUENCE(ROWS({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "})))

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, MATCH({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, 0)<>SEQUENCE(4))

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, MATCH({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, 0)<>{1;2;3;4})

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {1;1;3;3}<>{1;2;3;4})

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {FALSE;TRUE;FALSE;TRUE})

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

Back to top

Get the Excel file

how-to-extract-a-list-of-duplicates-from-a-columns-in-excel_version5.xlsx