Extract a list of duplicates from a column using array formula
Table of contents
Extract a list of duplicates from a column using array formula
Excel 2007 array formula in C2:
Copy cell C2 and paste it down as far as needed.
Remove #num errors:
Copy cell C2 and paste it down to D20.
Earlier Excel versions, array formula in C2:
How to create an array formula
- Select cell C2
- Copy/paste array formula
- Press and hold Ctrl + Shift
- Press Enter
How this array formula works
Step 1 - Show a duplicate value only once
=INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))
COUNTIF(C1:$C$1, List1) contains both a relative and absolute reference (C1:$C$1) to a range.
When you copy a cell reference like this the cell reference expands.
COUNTIF(C1:$C$1, List1) returns this array in cell C2: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
Step 2 - Filter values in List1 having duplicates
=INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))
COUNTIF(List1, List1) counts the number of cells within a range that meet the given condition. The returning array is (2, 1, 1, 2, 1, 1, 2, 2, 1, 2, 2, 1, 2, 1, 1, 2, 1, 1, 1, 1).
COUNTIF(List1, List1)>1 returns this array: (TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE).
IF(COUNTIF(List1, List1)>1, 0, 1) returns (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0).
Step 3 - Calculate arrays combined
=INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))
COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>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
=INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))
MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>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) is 2.
Match returns the relative position of an item in an array that matches a specified value
Step 5 - Return duplicates
=INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))
becomes
=INDEX(List1, 2) is Federer, Roger.
Index returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Final thoughts
When formula in c2 is copied to c3 the refence changes.
Example
The formula in c2: =INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))
Then copy the formula to C3.
The formula references changes: =INDEX(List1, MATCH(0, COUNTIF(C2:$C$1, List1)+IF(COUNTIF(List1, List1)>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 remaining values.
Named ranges
List1 (A2:A20)
What is named ranges?
How to implement array formula to your workbook
Change named ranges. If your duplicates list starts at, for example, F3. Change C1:$C$1 in the above formulas to F2:$F$2.
Download excel sample file for this tutorial.
how-to-extract-a-list-of-duplicates-from-a-column-in-excel.xlsx
(Excel 2007 Workbook *.xlsx)
Functions in this article:
MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise
Extract duplicates using conditions
Array formula in cell A6:
Download excel *.xlsx file
Extract duplicates using conditions.xlsx
Related posts:
Extract a list of duplicates from two columns combined using array formula in excel
Extract a list of duplicates from three columns combined using array formula in excel
Extract a list of alphabetically sorted duplicates from a column in excel
Filter duplicates from two columns combined and sort from A to Z using array formula in excel
Recommended categories
No mention of Nadal
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
(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:
Download 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...
)