Find Duplicates Items in Combine Columns in Microsoft Excel





To find the duplicate items in combined columns we use the “COUNTIF”, and “IF”formulae in Microsoft Excel 2010/2013.

 

IF:-IF condition allows us to specify multiple conditions in a cell. It helps us to create the flexibility of the function in Microsoft Excel 2010 & Microsoft Excel 2013.. Upto a maximum of 64 conditions can be placed inside an IF function.

Syntax of “IF” function: =if (logical test, [value_if_true], [value_if_false])

 

COUNTIFS:This function is used to return the count of same entry in the multiple range..

Syntax of “COUNTIFS” function: =COUNTIFS (Criteria_Range1, Criteria1,Criteria_Range2,Range2)

 

Let’s take an example and understand how we can find duplicate items in combined columns In Microsoft Excel 2010 & 2013.

Example: – We have a list of names in range A2:A28, in which one name is entered many times. We need to find all the names which are entered more than once.

 

img1

 

To do this, follow the below mentioned steps:-

  • Select the Cell C2, and write the formula to find the duplicate entry.
  • =IF(COUNTIFS($B$2:$B$28,B2,$A$2:$A$28,A2)>1,”Duplicate”,”Unique”) Press enter on the keyboard.
  • The function will return the duplicates – for example there are 2 entries for Aaron on 10th Feb.
  •  

    img2

     

  • To Copy the formula in all cells press the key “CTRL + C” and select the cell C3 to C28 and press key “CTRL + V” on your keyboard.

 

img3

 

To convert the formula into value use the “Paste Special” option:-

  • Select the range C2:C28, Copy by pressing the key “CTRL + C”.
  • Right click on the mouse select “Paste Special”.
  • In the dialog box select values and click on ok.
  • The formula will be replaced with values.


19 thoughts on “Find Duplicates Items in Combine Columns in Microsoft Excel

  1. “Have a sorted single column of about 600 cells, with alpha data (stock symbols).

    Each time updated, the column contains duplicate entries….about 25 to 50 stocks are listed two or more times.

    I manually sort the column. Then must tediously go through, eyeball the column and delete the duplicate rows.

    Now, in some sort utilities external to Excel, you can specify that duplicates be dropped.

    Is there a sort function or sort formula that can sort such a column in Excel, dropping duplicates/extra rows or cells (or any number of multiple instances of a value)? I do want to keep the first instance of each value, just not the subsequent duplicates.

    Seems like such an easy thing, but absolutely cannot find anyone who has a solution! “

  2. Could you please let me know the formula to check for duplicates from different sheets of the same Workbook. It would be great if you could give me a formula to prevent duplicate entries in different sheets.

  3. Great, but is there also a way of it automatically dropping duplicates/extra rows or cells (or any number of multiple instances of a value)? I do want to keep the first instance of each value, just not the subsequent duplicates. 

  4. “This will require you to insert a column next to the data you wish to sort, but it will provide you a list of the unique items in a range.

    From John Walkenbach’s “”Microsoft Excel 2000 Formulas”” (www.j-walk.com), p. 356

    “”If you have a single-column range named Data, the following array formula returns a list of the unique items in the range:

    {=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT(“”1:””&ROWS(Data))),MATCH(Data,Data,0),””””),ROW(INDIRECT(“”1:””(Data)))))}

    Play around with it, and don’t forget to enter it as an array. The formula is looking for unique items, and then placing them into a contiguous list without blank rows inbetween. If returning blank rows doesn’t bother you, IF(COUNTIF($A$1:A1,A1),A1,””””) copied down the inserted column next to your data will also work. “

  5. “Kindly advise me the formula for finding the duplicates in same work book but different spread sheets.

    For ex. I have a work book which has more than 10 sheets ,if i enter any data which is present in any of the other sheet it should pop up a duplicate .

    and also another doubt,

    For ex.i have 8 to 9 colums to be filled in mandatory ,if i do not fill any one of the colum the excel should give a message box saying pls fill this colum .”

  6. “Could anyone please give an idea where can I find answers or a clue to my question about finding duplicates
    in a column of cells.
    Thanks a million for your help.”

  7. “The posted answer doesn’t seem correct to me.
    Shouldn’t the formula entered in E1 be: =IF(COUNTIF($C$1:$C$12,C1)>1,””Duplicate””,””Unique””)?
    Change: $C$1:C1 -> $C$1:$C$12
    Even the screenshot shows incorrect results.”

  8. “If your order numbers are in column A (from A1 down), then enter the following in B1:

    =countif(A:A,A1)

    Copy that down as far as the bottom of your data.

    It will tell you how many entries in the list in column A are the same as the one it refers to.

    Hopefully most will be ’1′, so you might want to filter the list, and just show entries in column B that are greater than 1 to find the errors.

    Hope that helps, “

  9. “Depends on what you want to do!

    The formula stated in the tip will flag the first time that a duplicate entry appears. This is useful if a dataset is being populated, and you want to know if someone (or something) enters a duplicate. The original entry will not be flagged since it is (presumed to be) correct.

    Your forumula is similar to the one I posted above. This catches any duplicates. Logically, if one entry has a ‘two’ beside it (two copies of that entry) then another must also have a ‘two’ beside it (the other member of the pair).

    Both are useful, depending on what you want to do. “

  10. “If you mean you want to highlight a specific number (or any number within two bounds), then yes.

    Just use conditional formatting under the Format menu.”

  11. “Thanks for your reply.

    Conditional formatting will only go to three numbers in my Excel, is there any way of finding and highlighting five specific numbers at once? “

  12. “Not sure I understand.

    Do you mean that you can only have three alternative conditional formats? If so, then I think that is you lot without getting into a VBA solution.

    However, if you mean that you want to apply one format to any occurance of any of five numbers, then you can definately do that.

    Probably best to use a ‘Formula Is’ type conditional format, and construct it using OR statements:

    Select cell A1 (say), then enter the conditional format:

    =(OR(A1=12,A1=23,A1=45,A1=56,A1=96))

    Replacing 12,23,45,56, and 96 with whatever you are looking for.

    Then copy the *formatting* from cell A1 to whatever range you want to check.

    HTH, “

  13. “The statement given:
    =IF(COUNTIF($C$1:C1,C1)>1,””Duplicate””,””Unique””)

    Is fine for a small run of a few hundred, but when you do 53,000 rows presorting of what you want is quicker.

    By this I mean if you have to remove duplicate phone numbers from a table in Excel.
    A B C
    15131234567 John Doe
    15131234567 John Smith
    15131255567 Jane Doe
    15131234567 Jane Doe
    15135551234 Jane Doe
    15135554444 John Doe
    15135554343 John Smith
    15135551122 John Smith

    Names can be duplicate because there can be lots of John Smiths that are not the same person or the same person can have more than one phone line. But there can only be one phone number used period. You want to remove Duplications of that phone number or find out which names have the same phone number so you can resolve which one is correct.

    The Pre Sort would be sorting the Phone numbers first with a regular sort.

    Then use a Simple function of:

    =IF(A1=A2,””Duplicate””, “”Unique””)

    This checks the current row against the next row.

    Trust me when doing 53,000 + rows it was a difference of 3 seconds for doing about 50,000 comparisons verses 1.5 Billion (with a B) comparisons. That’s 200 times difference. Oh and it will have to go back through your 600 second I.E. 10 minute computation every time you sort because you have automatic calculation on.

    But if you are running this on someone else’s computer watching a 2.4Ghz with 512megs of ram fall over and crawl is priceless. “

  14. as we r entering data in cell A:A or we r going to enter any duplicate record in it, can it possible excel give us response about duplicate data.

  15. “In what way is your query different from Humberto’s above (see my reply of August 13, 2003 5:28 PM EST).

    Please can you specify how or why that solution does not work for you.”

  16. “Adding to Bob Hobbs’ tip:

    =IF(A1=A2,””Duplicate””, “”Unique””)

    Copy the column where the above formula is entered
    Paste Special (Value) back to the same column
    Sort on this column

    All the dupes are at the beginning; simply select and Delete all duplicates. Easy even for large files.”

  17. Great tip. Thank you. I have been searching for alternatives to FREQUENCY/MATCH with multiple repetitions of concatenated columns to determine a count of unique pairs in two columns. Thanks to this tip it is much simpler, now…
    =SUMPRODUCT(1/COUNTIFS(Range1,Range1,Range2,Range2)) No need for repeated concatenations now.
    Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *


7 + three =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>