## Extract duplicate values with exceptions

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

Comments(0) Filed in category: Duplicate values, Excel

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

**Filter duplicate values using critera**

Comments(0) Filed in category: Duplicate values, Excel

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

**Extract a list of alphabetically sorted duplicates from a column**

Comments(1) Filed in category: Duplicate values, Excel, Sort values

Filter a unique distinct list:

**5 easy ways to extract unique distinct values**

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

#### Download excel *.xlsx file

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

### Category: Duplicate values

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 […]Comments(41) Filed in category: Duplicate values, Excel

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 […]Comments(7) Filed in category: Duplicate values, Excel

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 […]Comments(4) Filed in category: Case sensitive, Duplicate values, Excel, Unique distinct values

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]Comments(4) Filed in category: Duplicate values, Excel, Unique values

How to extract a list of duplicates from a column in excel

Edit: A better shorter easier array formula in this post. Here is a solution (Column C) to extract duplicates from […]Comments(4) Filed in category: Duplicate values, Excel

Excel udf: Remove duplicates from a large dataset

Aamer asks: I have a sheet with 3000 rows of invoice dates that are out of order. There could be […]Comments(3) Filed in category: Duplicate values, Excel, Unique distinct values

Extract duplicates and their adjacent values

Question: How do i only put duplicate cell values from a list into another new list and their adjacent values? […]Comments(3) Filed in category: Duplicate values, Excel

Remove duplicates on same date in excel

Question: Column A1 Has dates Column B as data A1 : 1/1/2010 : 5000 A2 : 2/1/2010 : 4000 A3 […]Comments(3) Filed in category: Dates, Duplicate values, Excel, Unique distinct values

Filter duplicate words from a cell range in excel (udf)

AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]Comments(2) Filed in category: Duplicate values, Excel

### 3 Responses to “Extract duplicate values with exceptions”

### Leave a Reply

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

<code>your formula</code>

**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: