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 3. Total comments: 138

  1. 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?

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

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

  4. HI ,

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

    Thanks

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

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

  6. 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?

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

  8. 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!

  9. NOT WORKING

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

  11. 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?

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

  13. hi, this is really helpful

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

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

  16. 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?

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

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

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

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

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

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

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

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

  25. 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,,,

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

  27. thnx, it war really helpfull!

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

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

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

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

  32. Hi. I havea concatenate formula where I want an error to be shown when there are duplicates in the column. This doesn't seem to work.
    Sean

  33. I have a similar problem but with multiple criteria. How to identify an entry in an active cell is duplicate or not based on dual matching criteria?

    Regards,
    A.V.R. Murty

  34. thanks for your SPAM !

  35. list of top packers and movers in delhi

  36. nice toturial.

  37. How to avoid duplicate entry in multiple column.
    For Example:

    COL_A COL_B COL_C COMMENT
    JOB_A 1 2 Data is ok
    JOB_B 1 2 ok Although there are duplicate data in COL_B and COL_C
    COL_A 1 1 ok Although there are duplicate data in COL_A and COL_B
    JOB_A 1 2 NOT OK because exacly similar data is there in the first raw.

  38. I am inputting just a straight number i.e. 1150600542
    as an example number so nothing irregular.

  39. I want the whole of the A columns to restrict duplications and have put in the following formula but it does not seem to be working:-
    =COUNTIF($A:$A,A1)=1

    Is this wrong?

  40. Thank you.
    This was exactly what I was looking for.
    Saved me a lot of manual time and effort.

    Simple and easy to understand steps for a novice like myself.

  41. What is the best way to do the following;
    allocating employees to vehicles lets say vehicle number 51. If 51 is selected for more than 4 employees to go to a site an error is generated to say to the user to select another vehicle as this one is full

  42. After you've removed the duplicates, if you want to send an email to the list, the emails must be comma separated. You can turn a column into a list using this in-browser tool:
    http://convert.town/column-to-comma-separated-list

  43. Is day a way to prevent similar entries? What I mean is, I would like an alert to appear if a part of it already appeared before. For example:
    I have a table with product specification:
    1. coolproduct 17B
    2. thing 19C
    3. whatever 120

    Is there a way for an alert to pop up if I put in "coolproduct 19A" and suggest that a similar entry already exists?

  44. yes correct but still remain same problem using with copy-paste,
    how to useful with copy-paste?

  45. Hi ,

    I have a series of web URLS in an excel . I want the duplicates to be stopped from being entered . Could you tell me how to do that ..

  46. How to prevent duplicate numeric and text entries in excel column, only unique numeric and text data allowed. above formula: example =countif($D:$D,D2)=1 is working for both numeric and text value to prevent duplicate.
    My question are below:
    1. How to prevent duplicate only Numeric entries in excel column, only Unique Numeric data allowed.
    2. As well as how to prevent duplicate only text entries in excel column, only Unique text data allowed.

    Kindly do the reply needful.

    Regards
    Pradeep Kumar

    1. Please specify what result you want to get. This formula works for text as well as for numeric data.

      If you want to allow only unique numbers, text can be repeated, then use this formula:
      =OR(ISNUMBER(D2)=FALSE, countif($D:$D,D2)=1)

      If only unique text is allowed (numbers can duplicate), then the formula will be as follows:
      =OR(ISNUMBER(D2)=TRUE, countif($D:$D,D2)=1)

      If you want to get something different, please give me more details.

      1. f you want to allow only unique numbers, text can be repeated, then use this formula:
        =OR(ISNUMBER(D2)=FALSE, countif($D:$D,D2)=1)

        hi, i still can;t get it going with this formula.

      2. It is working with this formula =OR(ISNUMBER(D2)=TRUE, countif($D:$D,D2)=1) in excel 2010. thank you.

      3. Dear,

        Your trick is nice but i m facing problem that if somebody use cut copy paste the trick is gonna fail.

  47. What if I already have a "data validation" for a list in that column, but still need to prevent duplicates, how do I fix it ?

    Is there another way without using Data Validation ?

  48. I have a question, would appreciate if you could help. I am applying the rule to a list of names, however, it doesn't recognise the entire content of the cell ie, if I already have an Adam Smith in my list and I would like to add Adam Jones, it recognises 'Adam' and will prompt an error. Would you be able to advise?

    Thanks!

  49. This doesn't work if you copy and paste the value?? How do I change this?

    1. Unfortunately, Data Validation prevents invalid entries only when you type data directly in a cell using the keyboard. The pasted values are not checked for validity.

      If you want to block the insertion of values which are already in the list from the clipboard, a special VBA code is needed. You can ask for a macro on http://www.mrexcel.com or http://www.excelforum.com forums.

      Please note that a quite complex code will be required to process all the variants.

  50. Doesn't seem to work if the data column to be checked for duplicates is populated via a linked combo box, or other formula. Is there a way around this?

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 :)