## Extract duplicate values with exceptions

*Article updated on September 19, 2017*

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

**Formula in cell E2:**

This post explains how to extract duplicate values using a condition:

Create a list of duplicates where adjacent cell value meets a condition

Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]

You can also use more than one condition, this article shows you how:

Filter duplicate values using critera

The following worksheet allows you to search for duplicate names using a date range (cell B1 and B2) and a […]

If you want a list sorted from A to Z, read this article:

Extract a list of alphabetically sorted duplicates from a column

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula […]

Filter a unique distinct list:

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

#### Download excel *.xlsx file

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

Extract a list of duplicates from three columns combined using array formula in excel

Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array […]Filter unique distinct and duplicate values from a large data set in excel 2007

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]### 3 Responses to “Extract duplicate values with exceptions”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] 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: