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.
Table of Contents
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:
1.1 How to enter an array formula
- Copy above array formula.
- Double press on cell F3.
- Paste formula to cell F3 (CTRL + v).
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- 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.
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), "")
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:
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 "}.
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
Extract a list of duplicates from a column
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
Filter duplicate values in a range using “contain” condition
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
Sum number based on corresponding unique value
The formula in cell E14 adds a number from column C if the corresponding value in column B is unique […]
Create a list with most recent data available
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Highlight duplicate values in a cell range
The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]
The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
3 Responses to “Extract duplicate values with exceptions”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[…] https://www.get-digital-help.com/2017/08/19/extract-duplicate-values-with-exceptions/ […]
Hi Oscar,
i've really tried to understand why this formula - as awesome as it is - wouldn't filter triple (etc.) values in list A. If you enter one more Federer in A he appears in E. The version of this kind of formula without the second countif clause (inside the if that kills the unique values) lists anything that comes up more than once just fine, not only duplicates. Now, i've come up with the following formula, which actually seems to work:
=IFERROR(INDEX($A$2:$A$20;MATCH(0;COUNTIF(E1:$E$1;$A$2:$A$20)+IF(COUNTIF($A$2:$A$20;$A$2:$A$20)>1+(COUNTIF($A$2:$A$20;$A$2:$A$20)-COUNTIF($C$2:$C$3;$A$2:$A$20))*COUNTIF($C$2:$C$3;$A$2:$A$20);0;1);0));"")
But i'm no excel expert, and it has a feel of not being the most elegant solution at all... Any ideas? Thanks so much for all the help!
Hi Stephan
Thank you for telling me and thanks for your formula.
This regular formula seems to work as well: