E-Mail 'Extract a list of duplicates from a column' To A Friend
Email a copy of 'Extract a list of duplicates from a column' to a friend
Loading ... Loading ...70 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.
Hi,
I want to thank you for the great fuction you created. They are very handy.
One technical question. I have some problem to make array that are include more than 500 cells. Do you know how this can be fixed. My guess will be that this is some memory limitation, but I am speculating. I look in the vbs code and did not see any size limitations there.
Your reply will be highly appreciated.
Krassy
Hello, I have the same issue with limit of just over 500, I am using old version of excel, perhaps this is the case, can you tell me if it was fixed and what fixed it, thank you
@Krassy,
Off the top of my head, I do not see where there should be any limitation as your are reporting. If you would like, you can email me your workbook along with a description of the steps you take that produces the problem (so I can duplicate them here) and any error messages you get (so I know what to look for) and I will see if I can uncover the problem. My email address is rickDOTnewsATverizonDOTnet... just replace the upper case letters with the words they spell out.
No mention of Nadal :D I'm guessing you're a Federer fan Thumbs UP
Hello,
I have duplicate data spread between C3 and W22. I would like to list the distinct values in C3:W22 in another worksheet(2003). could you please help me.
Regards,
Kamal
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... :)
This is close to what I need. I have three lists of email addresses. If an email address appears in all three (not two out of three) lists then place it in the duplicate column. Also I need all three named ranges to be dynamic. Can you help?
Peter Voss,
See this post: Filter values that exists in all three lists
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))
[...] in Compare, Excel, Search/Lookup on Sep.27, 2012. Email This article to a Friend Peter Voss asks:This is close to what I need. I have three lists of email addresses. If an email address appears in [...]
Hi there,
I am using Excel 2011 on a Mac and keep getting errrors...
Vanessa,
I don´t have a mac, perhaps it is possible to disable error checking rules:
https://www.addictivetips.com/microsoft-office/show-error-on-formula-referring-to-an-empty-cell-in-excel-2010/
This is not working for me; I keep getting #NAME showing up where the duplicate list should be.
[...] 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... :))
I said before that you're a genius
But I want the previous version (office 2003)
I said before that you're a genius
But I want the previous formula (office 2003)
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.
Hi Oscar,
I am having issues with the listing formula, I have a table as below:
S.no Month Date Session Name Session Duration Trainer
1 Sep 05-Sep-14 The Art of Tactful conversations 0.5 ABC
2 Sep 09-Sep-14 Managing Client Expectations 0.5 DEF
3 Sep 11-Sep-14 Creativity and Lateral Thinking 0.5 SBC
4 Sep 15 Sep14 and 16 Sep Shaping Customer Agenda 2 days ABC
5 Sep 16-Sep-14 The Art of Presentation Skills 0.5 SBC
6 Sep 23-Sep-14 Happiness @ Work 0.5 ABC
7 Sep 25-Sep-14 Emotional Intelligence 1 day DEF
8 Sep 29-Sep-14 Influencing and Negotiation skills 0.5 DEF
9 Sep 29-Sep-14 Planning and Prioritisation 0.5 SBC
10 Sep 30-Sep-14 Strengthening Workplace relations 0.5 ABC
I need to get a list of trainings done if i update a trainers name.
This has to be done in a seperate workbook.
Please help.
Thanks
DS
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/extract-a-list-of-duplicates-from-a-column-using-array-formula-in-excel/#exceptions
[…] https://www.get-digital-help.com/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:
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/excel-pivot-tables/#count
Thank you for your comment.
[…] Filter duplicate words from a cell range in excel (udf) […]
Hi
How would i go about just looking if data matched in just one column ?
so get the row extracted based on matching values in column 3?
richard reeves
Try this:
=INDEX($A$2:$D$25, SMALL(IF(COUNTIF($C$2:$C$25,$C$2:$C$25)>1,ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1),ROW(A1)),COLUMN(A1))
My example has only 10 and 11 in column C so all records are extracted. (10 and 11 are duplicates.)
Hi All,
I'm well aware that this article is almost 10yo but I happen to need this exact VBA code (or ideally one that will highlight the duplicate words from several strings texts across a selected range). HoweverI'm having issue getting this to work for me. I keep getting the "Syntax error message".
Any chance someone can help?
Thank you
Hi Ely
I keep getting the "Syntax error message".
The code shown in this article is now working.
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
Hi, I need little help, I have a data of thousand of customer accounts and their account maintaining branch Code. I have been assigned a task to extract branches list and its accounts details according to the given criteria;
I have to extract all those branches and its accounts where a customer have multiple accounts with one CIF# in same branch, for example A customer has CIF # 1234 in 001 branch (its a branch code) 10 accounts are linked with this CIF# in the same branch and there are 05 other accounts which are linked with this CIF but maintained in other different branches; I have to extract the data branches & the accounts where one CIF has multiple accounts in same branch.
here is my table for sample
Branch Code CIF# A/C#
001 123 001001230002151
001 123 002001230002351
001 123 003000123000546
002 123 004000123000445
Hi Dr,
I excited if you could help me work out how to approach the following challenge:
I have a table with repeated IDs in one columns, and dates of when they were admitted in another.
I need to calculate the time spend measure between two dates for the repeated IDs.
Many thanks direction on this one!
Great work! I've been searching for a couple of hours, and your formula, while intimidating and I don't really understand the logic, works.
I wanted to match just by 1 row in 3 columns B,C,D, so I changed it to the below formula. Just need to figure out how to handle div\0 errors when there is NO duplicate and it would be perfect for me.
=IFERROR(IFERROR(LOOKUP(2, 1/((COUNTIF($E$2:E2, B3)=0)*(COUNTIF(B3, B3)>1)),B3), LOOKUP(2, 1/((COUNTIF($E$2:E2, C3)=0)*((COUNTIF(C3,C3)>1)+(COUNTIF(B3,C3)>0))),C3)),LOOKUP(2, 1/((COUNTIF($E$2:E2,D3)=0)*((COUNTIF(D3, D3)>1)+(COUNTIF(B3, D3)>0)+(COUNTIF(C3, D3)>0))), D3))