Comments on: How to prevent duplicates in a column in Excel

Today I'll tell you how to prevent duplicates from appearing in a column of your Excel worksheet. This tip works in Microsoft Excel 2013, 2010, 2007 and lower. Continue reading →

Comments page 2. Total comments: 136

  1. it's working well while typing but it doesn't work when I copy & paste the same value, shows no error, and paste the same value.
    can you sort our, please.

    thankks

  2. The column I wish to eliminate the entry of duplicates is populated by alphanumeric characters. The formula =COUNTIF($A$1:$A$20,A1)=1 does NOT work:

    - direct data entry (typing) meets automatic error message
    - copy date entry is permitted even if the same date already in the column.

    Excel 2016 - So what IS the correct, working method to prohibit duplicate entries?

    1. Hello Rottweiller!
      Unfortunately, Data Validation does not always work (it is mentioned in the last paragraph of the article).
      It works only when you put information directly into a cell and press Enter to record changes at the end. If you replace data in a cell by copying or pasting another cell, Data Validation does not see those changes and cannot ban them.
      I recommend using Conditional Formatting to see such duplicates.
      You can use this formula as a condition: =COUNTIF($A$1:$A$20,A1)>1.
      Alternatively, simply use standard Highlight Cell Rules -> Duplicate Values

      1. iam try but not working , any value enter coming error msg

  3. If I am copying the same data from another sheet to this column which I have validated,then this is not working out and does not throw any error.please help me in this

    1. Hello Himanshi!
      Please describe your problem in more detail.
      How were you checking the column?
      What data are you trying to copy and paste?
      Does the size of the copy area coincide with the size of the paste area?
      How is it connected with looking for duplicates?
      If you give more information, I will try to help you.

  4. i have excel sheet with contact number this should be duplicate which was repeating again and again

  5. Strange, everything seems fine. but no dialog box pop u if duplicate value entered.

  6. Thank you alexander

  7. YOUR FORMULA SO GOOD AND WORKING, GET CONNECT WITH YOU

    THANK YOU SO MUCH

  8. THANK YOU, IT IS GREAT.

  9. Hi,

    It is not applicable when pasting. why that is so?

    thx

  10. This worked for me, thanks! One question: When the alert shows up, is there a way to track which cell the duplicate is in? Sometimes we have so many entries that manually looking for it can take a while. Thank you!

  11. CORRECTION

    Hi There,
    Minor issue detected.

    For Excel 2013 the formula as mentioned in the article is not allowed.
    The seperator between the range and the criteria must be a semicolon, instead of a comma, like this :

    =COUNTIF($D:$D;D2)=1

    Making this minor correction made it finally work for me ;-)

    Thanks

  12. Hi There,
    Minor issue detected.

    For Word/Office 2013 the formula as mentioned in the article is not allowed.
    The seperator between the range and the criteria must be a semicolon, instead of a comma, like this :

    =COUNTIF($D:$D;D2)=1

    Making this minor correction made it finally work for me ;-)

    Thanks

  13. i want to extract the report where mobile number and DATE are same so dont consider duplicate. only consider duplicate mobile numbers which have different date.

    Date Mobile
    9/18/17 4:09 PM 923005254870
    9/18/17 6:45 PM 923005254870
    9/14/17 6:10 PM 923008435348
    9/14/17 6:29 PM 923008435348
    9/7/17 8:13 PM 923023689646
    9/14/17 6:55 PM 923023689646
    9/9/17 1:00 PM 923032819439
    9/12/17 2:28 PM 923032819439

  14. Hi,
    i have a list of data but all are 19 digits.
    Because of this, duplicates are not identified.
    Could you please share with us the solution.

    Thanks
    Anas

  15. Can this work across tabs? I am trying to prevent duplicate entries being used across 3 separate tabs, all saved in the single file. The cells are the same in each tab (ie. the same column) in each tab if this helps?

  16. Hello,

    It would seem that for this to work, you need to have a range with cells following one another. In my instance, my cells are individual, i.e. there could be 3 rows seperating them. This means that when i select the data for which i want the rule, it comes up as individual cells rather than a range.

    Any help is appreciated.

  17. thanks a lot for the explanation but i am a trainer and want to prepare a sheet that highlights the names of the trainees who got the training in past years, so that to avoid duplication in trainings

  18. how to validate name field in excel(cell should not accept number or special characters )?

  19. I have bulk entries in column $b1:$b450 , it also contains repeated text like "AAA" in cells B2, B5, B8, B10 so on.

    Now i want to give Sno in $A1:$A450, but i dont want that Serial number consider the duplicate text contain in other cells, it should treat 1 for all "AAA"

    Let me know the formula

    Thanks

  20. Hi, how do I avoid duplicate entries on multiple sheets. E.g I have listed invoices by month to month on a different worksheet tab (One excel spreadsheet but each tab has say Jan, Feb). I've applied the rule and it works but I want it to pick up too if there was any duplicate entry from previous months (e.g I'm entering data for Feb and I'd like to pick up if there was any same invoice number already entered in previous months or tabs). Thanks

    1. I have the same issue - did you find a solution?

  21. Am trying to prevent duplicates in a column of cells in our project log sheets. Each entry contains eight characters, with the first five being able to repeat, but the last three must be unique and sequential (i.e. JR157001, KR257002, ST457003, etc). We continue to get the last three duplicated with the first five being different and it throws everything into chaos when 10-15 entries later you duplicate an existing entry, then all outside files (other software) with the incorrect numbers have to be reworked, wasting a lot of time. I have tried COUNTIF and a few others, but there is always something no right with the formulas. Thanks.

  22. MUCH EASIER WAY:

    CLICK FILTER ADVANCED BUTTON

    SELECT COLUMN YOU WISH TO APPLY FILTER TO

    CHECK THE BOX LABELED "UNIQUE RECORDS ONLY"

    YOU ARE DONE, BOW THE DUPLICATES ARE ELIMINATED AND YOU CAN EMAIL YOUR PEOPLE

  23. HI ,

    Could you help me for combine multiple excel ( Car booking data) in one sheet.

    Thanks

  24. Is there a custome formula that would work in data validation for this?

    I've wasted hours so far... Please help!

  25. How to ignore the first character when avoiding duplicates.
    Example:

    A70000011
    B70000011

    Consider this as a duplicate.

    Also, not consider text a duplicate.
    Example:

    A--END--
    B--END--

    This would NOT be considered a duplicate.

    Any ideas?

  26. COPY AND PASTE FIX
    I thought this was going to be perfect for my needs, until I realised this method doesn't work if copy and pasting data.

    Ok, so this isn't a fix, but rather a solution that suited me and may suit others as well.

    I followed the guide at https://www.ablebits.com/office-addins-blog/how-to-highlight-duplicates-excel/

    if the link doesn't show or whatever, it's the article titled: How to automatically highlight duplicates in Excel once something has been typed.

  27. Hello, Thank you for the tutorial! I read the part where you mentioned you'd be posting another tutorial on how to check duplicate entries within the same worksheet & even another workbook. I was hoping that you had posted the tutorial & could direct me on where to find it.

    Thanks again!

  28. NOT WORKING

  29. I am happy after to learn excel command, how to avoid duplicate.
    I am thanks.

  30. I tried to do this so many times, i even installed the trim add-on, but it did not work. is there any other way?

  31. Hi,

    When i applied Data Validation then it shows some error which says "Name range that you specified can not be found". Can you please help me on this error pls

  32. hi, this is really helpful

  33. Hi, Thank you very much for the information above. It is very helpful. I have a specific question for removing the near match duplicates.
    eg. if particular column there are following entries and I want to keep only those with larger data point in the length.

    RSAsVAGIVGADEEAPPAPK
    SAsVAGIVGADEEAPPA
    SsVDDGNINLTDTSTSNK
    SsVDDGnINLTDTST
    sASAAAILEEDDSKDDmEFK
    TFsGIGFNLTEK

    In this case following entries
    RSAsVAGIVGADEEAPPAPK
    SAsVAGIVGADEEAPPA
    are the same but I need to keep RSAsVAGIVGADEEAPPAPK but not SAsVAGIVGADEEAPPA.
    How it can be done for complete column with various entries? same is true with
    SsVDDGNINLTDTSTSNK
    SsVDDGnINLTDTST
    I need to preserve the longer one and remove the shorter.
    Please help me with this.

  34. Please helped me a lot. I have a combination of data in 2 different columns which must be unique.

    Example of correct data
    emp id | Date
    101 | 27/05/2016
    101 | 28/05/2016

    Example of Incorrect data
    emp id | Date
    101 | 27/05/2016
    101 | 27/05/2016

    So I want to stop duplicate data in a combination of columns. I am trying to do but fail always. Please help me in this regard i really need for this ....

  35. Hi, This is really helpful, would like to check, what if the fields that I wanted to control are already an data validation(drop down) field?

  36. Hello,

    Im trying to remove the duplicates and duplicates has appear in the next column.

    Example:
    In column A name has the below Alex,Tom,Tej,Alex,Tom,Alex.

    Duplicates has to appear in the B column.

    please help me on this

  37. hi,
    i have just tried out this formula to one of my spreed sheet and it's great.
    However, when i type in a new invoice number on the column I've just selected it keeps picking up as duplicate. Also, how do i remove this format to re-set again? please need assistance asap.

    thanks - Vavine Pianta

    1. after failed many times until i disbear it worked by adding space before = and before 2
      like =COUNTIF($A:$A,A8) = 2
      try it

  38. How do we copy the entire data of my worksheet i'm asking this question because when i select and copy(ctrl+c), it says "That command cannot be used on multiple selections". Please Help

  39. I want to make one table which have no duplicate numbers & also it is random .
    Requirement :
    First input : 1 - 90 for same numbers came in first column
    Second input : 1 -90 random no came in 910 columns but not repeat

  40. I would like to stop duplicate value entry in a self generating list. Any idea how to do it.

  41. Hi,

    I am trying to prevent duplicacy by data validation but it is not applicable in case of copy-pase option. what i mean to say is when i copy the cell value and paste somewhere else in the same column it does not show the error message. however it shows when i am typing the same value. in this way my purpose is not fulfilled. plz guide in this regard.

    thank you
    banashree

  42. Suppose I want this formula to affect the value in the middle of a column up to a certain point. e.g. from D5577 to D10000. Because I already have some data in that column and want the formula to ignore that data.

  43. Input
    Column A Column B
    Grapes Fruits
    Grapes Fruits
    Tomatto Vegetable
    Apple Fruits
    Onion Vegetable
    Tomatto Vegetable
    Apple Fruits
    Mango Fruits
    Papaya Fruits

    Output
    Fruits 4
    Vegetable 2
    Anybody know the answers.

  44. It works for a new file created "email" colum with "UNIQUE" formula.

    I couldn't implement it to an existing file's "email" field after removing duplicates, wanted to convert it's email colum into a unique field, but didn't function.!

    Thanking you,,!

    AHMED IBRAHIM
    DUBAI L,,,

  45. how can i assign two validation on a single cell
    one is for list validation (means the data should be from that range)
    second i want to prevent them from repetition

    how can i do this ?
    P7 to P506 have GR# which are for list
    i want to prevent C column to not to repeat and should be from the P column

  46. thnx, it war really helpfull!

  47. I am encoding tons of household ID nos which consists 10 digits-4 digits-5digits. I have to ensure that no repetition of the said HH No. The formula is working just fine. However, when I Copy/Paste the whole HH ID No., the formula is NOT working. Any other way that whenever I copy/paste I get the same result of the used formula? Thank you.

  48. It did not work at first but after I unchecked the "ignore blank" box next to custom it worked. Very helpful. Thank you.

  49. It is not working in my excel sheet. please Help me..

  50. This has helped me a lot. I have a combination of data in 2 columns which must be unique.

    Example of valid data
    Date | Shift
    08 Aug | Night
    08 Aug | Day

    Example of INVALID data
    Date | Shift
    08 Aug | Night
    08 Aug | Night

    So I have to avoid entry of duplicate data in a combination of columns. I am not able to figure it out please help.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)