## 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 in column C.

**What's on this page**

## 1. Extract duplicates

**Update 2017-08-19! New regular formula in cell D3:**

#### This video explains how to use the formula and how it works

The following formula is an outdated formula, the above formula is smaller and better.

Array formula in D3:

### 1.1 How to enter an array formula

- Copy (Ctrl + c) above formula
- Double press with left mouse button on cell C2
- Paste (Ctrl + v) to cell C2
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys

Your formula now looks like this: {=array_formula}

Don't enter the curly brackets, they appear automatically.

Recommended articles

Array formulas allows you to do advanced calculations not possible with regular formulas.

### 1.2 How to copy the formula

Copy cell C2 and paste it to cells below as far as needed.

### 1.3 Remove #num errors:

Copy cell C2 and paste it down to D20.

Learn more:

Recommended articles

The IFERROR function lets you catch most errors in Excel formulas. It was introduced in Excel 2007. In previous Excel […]

### 1.4 Earlier Excel versions, array formula in C2:

The IFERROR function was introduced in Excel 2007, if you have an earlier version then use the formula above.

### How (the old) array formula works

#### Step 1 - Show a duplicate value only once

COUNTIF(D2:$D$2, $B$3:$B$21) contains both a relative and absolute reference (D2:$D$2) to a range.

When you copy a cell reference like this the cell reference expands.

COUNTIF(D2:$D$2, $B$3:$B$21)

returns the array

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

Recommended articles

Counts the number of cells that meet a specific condition.

#### Step 2 - Filter values in $A$2:$A$20 having duplicates

The COUNTIF function counts the number of cells within a range that meet the given condition.

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

returns

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

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

returns

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

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

returns

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

Recommended articles

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

#### Step 3 - Calculate arrays combined

COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1)

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

equals

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

#### Step 4 - Identify duplicates

MATCH(0, COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1), 0)

becomes

MATCH(0, (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0), 0)

returns 2.

Match returns the relative position of an item in an array that matches a specified value.

Recommended articles

Identify the position of a value in an array.

#### Step 5 - Return duplicates

INDEX function returns a value or reference of the cell at the intersection of a particular row and column, in a given range

INDEX($B$3:$B$21, MATCH(0, COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1), 0))

becomes

INDEX($B$3:$B$21, 2)

and returns "Federer, Roger".

Recommended articles

Gets a value in a specific cell range based on a row and column number.

#### Final thoughts

When the formula in c2 is copied to c3 the reference changes.

Example

The formula in c2: =INDEX($A$2:$A$20, MATCH(0, COUNTIF(D2:$D$2, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

Then copy the formula to C3.

The formula references changes: =INDEX($A$2:$A$20, MATCH(0, COUNTIF(D3:$D$2, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

Read more about absolute and relative cell references.

This makes it possible to avoid previous cell values (C2) and only calculate the remaining values.

## 2. Extract duplicates - Excel 365

Formula in cell D3:

### Explaining formula

#### Step 1 - Count values

The COUNTIF function counts cells based on a condition or criteria, this allows us to identify duplicate values.

COUNTIF(*range*, *criteria*)

COUNTIF(B3:B21, B3:B21)

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 "; "Blake, James "; "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 "; "Blake, James "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "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 - Identify duplicates

The larger than sign lets you check if a number is larger than 1, in other words, a duplicate.

COUNTIF(B3:B21, B3:B21)>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; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.

#### Step 3 - Filter values if count is larger than 1

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

FILTER(*array*, *include*, [*if_empty*])

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

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 "; "Blake, James "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "Almagro, Nicolas "}, {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE})

and returns

{"Federer, Roger "; "Davydenko, Nikolay "; "Federer, Roger "; "Davydenko, Nikolay "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "}.

#### Step 4 - List one instance of each value

The UNIQUE function extracts both unique and unique distinct values and also compare columns to columns or rows to rows.

UNIQUE(*array,[by_col],[exactly_once]*)

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

becomes

UNIQUE({"Federer, Roger "; "Davydenko, Nikolay "; "Federer, Roger "; "Davydenko, Nikolay "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "})

and returns

{"Federer, Roger "; "Davydenko, Nikolay "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "}.

### Duplicate values category

This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]

The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]

Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]

This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]

This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]

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

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 46 Responses to “Extract a list of duplicates from a column”

### 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.

**Contact Oscar**

You can contact me through this contact form

No mention of Nadal :D I'm guessing you're a Federer fan Thumbs UP

I tried this formula, it does not work for me - any way to figure out what I am doing wrong??

Sheila McGarrigle,

How to create an array formula

Select cell C2

Copy/paste array formula

Press and hold Ctrl + Shift

Press Enter

how to extract the unique distinct column from one sheet to another sheet in the same work book using formula.

audithya,

Extract a unique distinct list

Change cell references.

i changed the cell reference but the value is showing as " 0".

what i want is

sheet 1 - column A has 123412323422 values (Having blank cells).

i want this unique distinct values to sheet2 column A..

please provide the formula to get the values into sheet 2 from sheet 1.

thanqq

Audithya

added to the above one

i want the result in sheet 2 column A as 1234(without blank cells)

Here is an example workbook:

audithya.xlsx

Oscar,

I used this example workbook in my own application and it works great. I'm wondering if there is a way to filter the results and/or criteria. In the duplicates column I have a wide range of text-filled cells, which duplicate quite frequently. I would like to have this formula return only cells that begin with the letter "L", and also eliminate all duplicates. Is this possible?

Example

Column A, Sheet 1

APPLE

BAKER

LOT1

LOT3

FARM1

FARM1

TABLE

LOT1

LOT4

LOT3

Returning data to Column A, Sheet2

LOT1

LOT3

LOT4

(Not necessarily in a sorted order)

MikeB

Read this:

Filter unique distinct values beginning with a letter

thanqq oscar... :)

Hi Oscar..

I have two Sheets in a workbook. one column of the first sheet contains first sheet contains duplicate data. in the second sheet the column have the data which is extracted the unique data from the first sheet. what i want is the comparing these two columns data if they are equal then the next column result in sheet 1 will be come on the sheet 2

Ex.

Sheet 1:

ColA ColB

a Pass

a Fail

ab Pass

abc Pass

abcd Pass

abcde Fail

abc Fail

Sheet2: wants to be look like

ColA ColB

a Pass

ab pass

abc pass

abcd pass

abcde fail

this colB of sheet 2 extracts the data from colB of sheet 1 if sheet2!ColA=Sheet1!ColA.

Please provide the formula. I am poor in VBA

saiaudithya

Formula in cell B1, sheet 2:

Index(Sheet1!$B$1:$B$10,MATCH(A1,Sheet1!$A$1:$A$10,0))

[...] Re: Duplicates Originally Posted by proficient I want to find duplicates numbers in a range Duplicates Value A B C D 1 1 1 4 2 2 2 6 3 3 4 3 4 4 5 6 6 1 7 2 8 2 9 4 10 65 11 1 12 2 13 2 14 4 15 25 16 1 17 2 Spreadsheet Formulas Cell Formula D1 =COUNTIF($A$1:$A$17,C1) D2 =COUNTIF($A$1:$A$17,C2) D3 =COUNTIF($A$1:$A$17,C3) Excel tables to the web >> Excel Jeanie HTML 4 This seems to be what you are looking for: Extract a list of duplicates from a column using array formula in excel | Get Digital Help - Microso... [...]

It works for me.. Thanks men..

Will it really takes time to if the data is Big?

Tnx,

MIke, CPA

Jarvin Villones,

If you have a large data set it will take time. It all depends on your computer hardware.

Hiya Oscar,

Thank you for your awesome formula, its a bit too advanced for me to be honest but I do grasp the concept of it. I would like to ask you is it possible to encapsulate the formula in an if statement somehow? or as it is in my case "IF from London + IF this month + then PULL unique agent names" I did give it a try but the formula could not work :(

Thanks in advance!

Best Regards,

FB

Neophyte,

Read this:

Extract duplicates using conditions

Huge thank you for your fast response however this is truly outside my league :D (our office excel guru's league too as it seems)... I am trying to pull the "Unique" names rather than duplicates and I don't understand your formula in order to reverse it.

Any help on the subject will be highly appreciated!

THANK YOU!

Neophyte,

Array formula in cell A6:

Get the Excel *.xlsx file

Extract-duplicates-using-conditions_ver2.xlsx

The formulas can be smaller if you have space for a "helper column" in your sheet. The dates make the formulas complicated.

Hi Oscar

Extract a list of duplicates from a column using array formula, does not work for me ? I am using Ms Office 2012

Julio,

did you create an array formula?

Hi, very useful info here.. I can't seem to leave a comment on previous post "How to extract a unique distinct list from a column in excel" so i posted a reply to this thread instead. Sorry..

Anyways, my problem is I wanted to get the unique list only if one condition in one cell is True (Column B). I tried using if() statement but i guess there's something wrong. I know it's very easy for you.. Tnx a lot for your help.

Ex:

A B

1111 True

1232 False

1234 True

Lester,

Read this, I think it is what you are looking for.

Thanks a lot Oscar for your help... Just what i needed... :))

Hi Oscar,

I am using the unique formula you created in response to Neophyte in the comment above. However, I would like to copy the formula across instead of down. How would I modify the formula to accomplish this?

Thank you for the help!

Kyle

Hi Oscar,

Disregard my last question. I figured it out. I just changed the last row formula to column and it worked.

Thanks,

Kyle

How to convert(transpose) sing column to row.

Thanks,

Chethan kumar

How to convert(transpose) single column to row.

Thanks,

Chethan kumar

Reply

Hi,

Just wondering if I would use this formula to return a list so it would show all duplicates as one, and single entries as they are

ie This List: Green, Yellow, Red, Yellow, Blue, Black, Green, Yellow, Oragne, Blue, Green, Pink

To A list like this: Green, Yellow, Red, Blue, Black, Orange, Pink

Thanks

Got it, not to worry.

Iam trying to add this formula for my work but it doesnt work and it comes up with #Name? Please help

thnx

This function seems to ignore any entry in List1 that has only one entry... Is that correct?

Tried this function using a list from a different sheet in by workbook, for some reason it is listing the results in doubles. Any reason for this? Using Excel 2013.

Nate

Hard to say without seeing your workbook, you entered it as an array formula?

Hi Oscar,

Thanks for your formula, I would like to know how can the duplicate be shown in row and expand to the right? Please help!

Jackie,

(Press with left mouse button to see full size image)

The formula is the same:

=INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)) + CTRL + SHIFT + ENTER.

Make sure the cell ref (bolded) is pointing to the next cell to the left, however this means you can't enter the formula in the first column.

I was able to adjust this to my project, but man does it take a long time to calculate. I have a spread sheet with about 4000 rows and it can take hours to calculate. Is there a faster way of doing this?

Hi Oscar, stumbled across this formula and it's quality!!! Your formula nearly works perfectly for me but how do I introduce an exception? Your formula finds and retrieves duplicate tennis players names, however if I wanted to exclude the name "Federer, Roger" from my results how would I do this? The list I am checking for duplicates has some legitimate duplicate text which I need to exclude from the returned results, thanks

Ben,

Thank you!

I have added your question to this post.

Read this: https://www.get-digital-help.com/2009/06/22/extract-a-list-of-duplicates-from-a-column-using-array-formula-in-excel/#exceptions

Step 1: Add a new column next to your data-field column, called count

Step 2: insert 1 in the first field and drag-to cover the full length of the data-set so that you have count=1 for all rows

Step 3: insert a pivot table with data-field column and count column

Step 4: drag data-field column header to rows

Step 5: drag count column to values and select SUM() function

Now you can see the data-fields listed on the left side with cardinality against each one of them.

No complex formulas are needed to find repeating values.

Satheesh,

No complex formulas are needed to find repeating values.I am trying to provide all possible techniques, some people want a formula.

You don't have to add a new column containing 1 in each cell to identify duplicates:

https://www.get-digital-help.com/2016/07/04/excel-pivot-tables/#count

Thank you for your comment.

Is it possible to list all duplicates using FREQUENCY formula?

The formulas with countif doesn't allow to start from the very first row due to circular reference.

Hi Oscar, I am a fan of your page and your work. I really thank you for helping me a lot. Could you get the duplicate values only once and in turn get unique values? How would you do it? Example I have sheet 1:

column A, column B

dario perez

dario perez

dario arrieta

dario arrieta

dario sandoval

The result recorded in columns c and d would be:

column C, column D

dario perez

dario arrieta

dario sandoval

Thanks!

This formula actually doesn't bring back correct results. If an item is a duplicate but wasn't a result in the prior row it is missing it. I replicated tis formula for my file and tested the results., FYI