Comments on: How to find duplicates in Excel: identify, highlight, count, filter

The tutorial explains how to search for duplicates in Excel. You will learn a few formulas to identify duplicate values or find duplicate rows with or without first occurrences. You will also learn how to count instances of each duplicate record individually and find the total number of dupes in a column, how to filter duplicates, and more. Continue reading →

Comments page 2. Total comments: 146

  1. Hello,

    I am preparing my salary file in Excel and we have different sites and locations. Employees are often transferred from one location to another. We have 2 modes of payment - 1 official through payroll and some part as cash incentive for job completed.

    The accountant many a times forgets to delete the name from one location when he is transferred to some other site wherein he adds him again, this results in duplication of payment.

    Can you please help me the way to find out on how to check whether one particular person is not there in any other site.

    Will be waiting to hear a fruitful reply on the above

  2. Hello and thank you for your helpful tutorial.

    I have column A with duplicate data.
    To copy column A to B and leave the first occurrence
    I have entered the formula =IF(COUNTIF($C1:$C$7, $C1)>1, "", A1)
    Column C has various unique texts
    With this formula all occurrences appearing at the last row e.g: 280 at row 3 and 150 at row 7
    How do I get them at the first row e.g: 280 in row 1 and 150 at row 4?
    I have tried to change the >1 to 0 and <0 but it doesn't work
    I am using excel 2003
    Hereunder The result I get

    A B C
    1. 280 Red
    2. 280 Red
    3. 280 280 Red
    4. 150 Green
    5. 150 Green
    6. 150 Green
    7. 150 150 Green

    Regards

  3. Hi Alex!

    Thank you for sharing all of this formula. it is very helpfull. i got some question;

    how to count the total of duplicate item? for example;

    i know that there is a total of 4 duplicate bananas, 3 duplicate orange, 6 duplicate grapes & so on.. but how do i calculate how many fruits that has duplicate quantity?

    Thank you in advance!

    1. Hello!
      I think that your problem cannot be solved with a single formula. If column A contains items, column B contains the number of duplicates, then try the formula:

      =IFERROR(ROWS(UNIQUE(FILTER(A2:A10,B2:B10>1))), 0)

      Try to use the recommendations described in this article: Count unique values with criteria.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  4. I have a starting 6-digit sequence # in cell L2 and an ending 6 digit sequence # in cell M2. These represent ranges of sequence numbers. They could be a few hundred to a hundred thousand. L2 would be a specified job, L3 another and so forth, each job with unique sequence numbers. These ranges of sequence numbers go on through L40 and M40. I want to make sure that nothing in any of these ranges of numbers are duplicates.

    Right now I have =COUNTIF($L$2:$M$35,L2)>1 but this only recognizes the actual printed number and not the numbers that are hidden in the range. 748375(L2) to 749927(M2) for example. If another line contains 749926, that is an encroachment and should be flagged as a duplicate.

  5. Finally a clear listed formulas!

    I have already located duplicated cells with conditional formatting into highlighted cells.

    But which formula / method would work the best for finding duplicate values (names) across few rows, I was comparing two name lists which I have stripped every word into single cells.

    I I'd love to try one by one but it loads for so long every time I input anything.

    Thanks in advance!

  6. I have massive amounts of data in columns B and columns C. column B is lists of names and column c is just a location number like "1234" and that's it. I need a formula to filter out how many times column b and c duplicate and it to show me exactly who is duplicated and how many times.

    1. Hello!
      How to identify duplicate rows and how to count duplicates, you can read in this article above. Have you tried these recommendations? If this is not what you wanted, please describe the problem in more detail.

  7. Hello,
    I have several files in pdf and i would like to cross check using excel if there are duplicate refs. Is there any formula for it?

  8. HI.
    I want to compare two rows of number,find duplicate,but not triple numbers,only two same numbers,not more.
    please help.

    1. Hi!
      If I understand your task correctly, try the following formula from the first paragraph:

      =COUNTIF(A:A, A2)=2

      I hope it’ll be helpful.

  9. Hi,
    A condition, my each column has
    A1- Apple, B1- Apple, C1- Orange.
    A2- Mango, B2- Apple, C2- Mango.
    And so on, now I want in D1 and D2
    only single fruit names with “/“ as its separator.

    Pls help.

  10. Anyone have any idea how to highlight duplicates within the column itself across the entire sheet? Meaning to say the conditional formatting ignores duplicated between columns and just checks for duplicates within the column itself?

  11. Hello, I have a spreadsheet where I record sales for each day. So 29/11/2021 may contain =SUM:(M104, M108, M121) and then 30/11/2021 may contain =SUM:(M105, M107, M121). The "M121" is a duplicate in these formulas. Is there any way to find and delete these duplicates. Thanks

  12. Hi Alexander,

    How do I compare 3 sheets for duplicates and highlight them? (the 3 sheets is in the same workbook).

    For example, I want to check if Sheet1 A1:A5 have duplicates from Sheet2 A1:A5 and Sheet3 A1:A5?

    Alexander , thank you very much.

  13. I want to create a condititional formula to highlight the duplicate values in a column. And then I want to copy that formula to different/multiple columns on the same and diff sheets.
    2nd, when I type a question mark (?), then excel treats it as a duplicate, even when there is no duplicate ? in that column.

    Kindly help.

  14. How can i find the duplicate cell in different rows and multiple column?
    and delete them?

  15. Hello,

    Can you help me with my problem?
    How to remove duplicates in different column?

    original data It's like this:

    1 1 5 5 5 3 4 4

    and turn to this:

    1 5 3 4

    Remove the duplicates but retain the 1st one.

    Thank you in advance.

    1. Hello!
      All duplicate detection methods require data to be arranged vertically in columns. Therefore, first, transpose the data as described in this guide. Then remove the duplicates. For detailed instructions, see this article. Then transpose the data again.

  16. This is great! However, I'm thinking of multiplying a certain no. or cell using if function for duplicates. Say if k3 cell duplicates within k1:k6, e3*85%, otherwise e3=100%.

    Tried using this --> [=another formula*(if(countif(k1:k6,k3)>1,e3*0.85,e3*1)] but sadly errors.

    Would anyone know how to go about this? Thanks in advance!

    1. Hello!
      This formula works for me.

      =(A3+A4)*IF(COUNTIF(K1:K6,K3)>1,E3*0.85,E3*1)

      If you get an error, please describe the problem in more detail.

      1. You're Godsent. Got why it errors. I doubled the multiplication in the formula. Thanks so much! :)

  17. how can we identify the duplicate location/reference designators in the different rows?

    Example
    1:-
    R1 Duplicate in both.

    R1.

    R1,R11,R12,R35,R187,R196,R203,R220,R221,R230,R231,R232,R233,R245,R246,R272,R283,R292,R297,R303,R321,R322,R342,R343,R345,R346,R349,R386,R406,R409,R532,R544,R555,R811,R812,R1033,R1034,R1035,R1036,R1779..

    2:-
    R4 Duplicate in Both.

    R4,R6.

    R2,R3,R4,R5.

    3:-
    R5 Duplicate in Both.

    R2,R3,R4,R5.

    R5,R46,R47,R48,R49,R54,R58,R60,R61,R65,R75,R82,R83,R84,R85,R86,R87,R184,R189,R190,R191,R192,R193,R238,R239,R240,R241,R258,R265,R266,R267,R268,R275,R276,R277,R278,R286,R287,R288,R289,R293,R294,R295,R296,R298,R299,R301,R331,R332,R333,R334,R335,R336,R337,R338,R339,R341,R344,R351,R352,R353,R354,R355,R356,R357,R358,R705,R867,R921,R924,R977,R978,R980,R981,R1038,R1052,R1053,R1767,R1768,R1769,R1772,R1784,R1785,R1786.

    1. Hello!
      Split each cell. Use a comma as a separator. To do this, you can use the Split Text tool. Use Duplicate Remover - Find Duplicate Cells tool to find duplicates. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      If something is still unclear, please feel free to ask.

  18. Using a formula(s), is there a way to find duplicate rows with multiple columns but info in columns are not necessarily in the same order.where the examples below are considered duplicates even if they're not in order?
    A B C D
    LAX SFO ATL DUPLICATE
    PDX ATL LAX
    LAS ATL LAX
    JAX LAX PDX
    SFO ATL LAX DUPLICATE
    JAX LAS MCI

  19. Hello,
    First, great tutorial, very useful. I applied some of the formulas to solve a situation. I just needed to know if the data I load into an Excel table is duplicated, I don't need to know which rows are repeated, just to know the data is wrong so I can go to source to solve a problem I didn't know existed. The approach of the array formula that does not require a helper column seems the best option:
    =ROWS($A$2:$A$8)-SUM(IF( COUNTIFS($A$2:$A$8,$A$2:$A$8, $B$2:$B$8,$B$2:$B$8)=1,1,0))
    But then I came across a situation. Sometimes one of the columns can have a blank value, when that happens the formula identifies that row as not unique and returns a wrong value. I could fix the situation with this formula:
    =ROWS($A$2:$A$8)- SUM(--( COUNTIFS($A$2:$A$8,$A$2:$A$8, $B$2:$B$8,choose({1,2},$B$2:$B$8,""))=1))
    As you can see, I added the "choose" function with {1,2} as the index num so the "Choose" function returns both, the complete range that is used as criteria plus the empty space value "" counting correctly the blank cells when needed.
    Another change I did was the use of "--" instead of the "if" function, but that doesn't change the result.

    1. Hi,

      I have 1500 lines data, I have to identify the duplicate with B collom and C collom,if that should match cell details and C cell details,how can I apply the formula for identifying the duplicate with same details matching with more than one.

  20. Dear Sir/Madam,

    Is there a way to find duplicate values in a single cell in MS-Excel? I'm working on a CSV file where there are duplicate values occurring in a single cell only. Can you please help?

  21. How to find last 4 digit duplicate value and marked. is there any formula. e.g.
    JVC2020TDL2946
    BV2020TDL2946
    JVC2020TDL2947
    BV2020TDL2947
    JV2020TDL0224
    JV2020TDL0225

    1. Hello!
      You can extract the last 4 digits into a separate column using the RIGHT function. Then look for duplicates in that column as described in the tutorial above.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Thanks Sir
        for your reply and i have also tried this option it will work properly.

  22. Hi,

    I would need some help regarding this issues:
    1. I have a list with names, I had found the duplicates. My task is: Using a single formula, detect all of the duplicates in the above list. If the term has already appeared in the list, then your formula should display it on the same row, if the term has not already appeared on the list then your formula should display N/A.

    Name
    Cristina
    Ioana
    Florin
    George
    Cristina
    Cosmin
    Neculai
    Alina
    Florentina
    Andreea
    Laura
    Alina

    2. I need a formula for completing this task: Create a formula that returns only the characters that appear after "X" for the given character strings. The same formula must work for the whole column!
    Example: 45629X421 421

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      To return all characters after "X" use the formula

      =MID(B1,SEARCH("X",B1,1)+1,100)

      I hope it’ll be helpful.

    2. I found the formula for task 2.
      Any thoughts for the 1st task?

      Thanks!

  23. How do i get collegue names if website duplicates. Let say first 3 people are at same company (same website) So i need to get collegue name such as

    Sam - Nick
    Nick - David
    David - Sam

    How do i get them.

    Full Name Collegue Name Website
    Sam abc.com
    Nick abc.com
    David abc.com
    Peter 123.com
    Ann 123.com

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      {=IFERROR(INDEX($B$2:$B$12, SMALL(IF($D2=$A$2:$A$12, ROW($B$2:$B$12)-1,""), COLUMN()-4)),"")}

      This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter. Line 1 is the title of the table. Column A is a list of websites. Column B is a list of names. Column D is the website where you want to find the names of colleagues.
      You can read more about this formula in the article: How to VLOOKUP multiple values in Excel
      You can combine names in one cell with delimiters using the formula

      =TEXTJOIN("-",TRUE,J2:N2)

      I hope my advice will help you solve your task. If something is still unclear, please feel free to ask.

  24. Hello,
    sorry, how to find the last duplicate value in row ,
    such as:
    A B C D
    2 4 3 2

    i want to find the number "2" that is from column "D"
    tnx..

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IFERROR(INDEX((IF(COUNTIF(A1:F1,A1:F1)>1,A1:F1,"")),,MATCH(TRUE,(IF(COUNTIF(A1:F1,A1:F1)>1,A1:F1,""))<>"",0)),"")

      I hope I answered your question. If something is still unclear, please feel free to ask.

  25. I am creating a quiz bank. I want to find duplicates within a row and then be able to copy the formula down a column. I tried using the formula =IF(COUNTIF(C1:I1,), "duplicate", ""). However it only returns duplicates of the first cell. I need to know if ANY of the cells in that row are duplicates. Please help!

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(SUM(--(COUNTIF(C1:I1,C1:I1)>1)) > 0,"duplicate","")

      After that, you can copy this formula down along the column.
      I hope it’ll be helpful.

      1. It works perfectly! Thank you SOOOOOOOO much!

    2. Sorry I just realized the formula i put in above is not the formula I am using. It is =if(countif(C1:I1,C1)>1 "duplicate", "")

  26. If the range is not continious, example A1:A5 and B1:B15 then how to check if the 15 values in these cells have duplicate or not

  27. Quick Question, Is there a way to set the 1st row of duplicate records to "Parent"
    I am using this formula: =IF(COUNTIFS($A$2:$A,A2,$B$2:$B,B2,$C$2:$C,C2)>1,"Child","Parent")
    Row 1 - Parent
    Row 2 - Child
    Row 3 - Child
    instead of
    Row 1 - Child
    Row 2 - Child
    Row 3 - Child
    Help is greatly appreciated.

    1. Hello!
      If I understand your task correctly, you need to set the first occurrence of a duplicate as Parent" marking the rest of them as "Child".
      Assuming that your table has no header, here is the right formula for you:

      =IF(COUNTIF(A1:$A$26,A1)>1, IF(AND(COUNTIF(A1:$A$26,A1)>1, MATCH(A1,$A$1:$A$26,0)=ROW(A1)), "Parent","Child"),"")

      If however, there is a 1-line header, the formula should be modified a little to look like the one below:

      =IF(COUNTIF(A2:$A$27,A2)>1, IF(AND(COUNTIF(A2:$A$27,A2)>1, MATCH(A2,$A$2:$A$27,0)=ROW(A2)-1), "Parent","Child"),"")

      Hope this is exactly what you need.

  28. how to find duplicate in a single row.

    example:

    Row1: 23 44 42 44 53 23 this row should be tagged as dulicate

    1. Prakash:
      Svetlana recently wrote a very good article on this topic. The article was published here on Ablebits on April 26.
      Enter "Find duplicates" in the search box and you'll see the article "How to find duplicates in Excel: identify, highlight count, filter, and more". Click on that link and once you've read the article you'll be able to accomplish what you want.

  29. Is it possible to get the number the duplicate in order that they appear?
    For example
    123,1
    124
    123,2
    123,3
    125
    123,4

    1. Frank:
      I don't see any duplicates in this data.
      Are you asking to match the first three digits?

  30. Hi,

    Thank you for all of the information on here.

    I am experiencing a range limit on the use of this calculation? For some reason it is only letting me check 20 rows, anything above that is returning a result of 0. Is this normal?

    What I really want to do is check an entire column in a table for values (there is about 1000), but as soon as i do this the returned value is zero. If it is less than 20, the returned value is acurate.

    I am using;
    =SUM(IF(ISTEXT(A28:A37)*COUNTIF(A28:A37,A28:A37)=1,1,0)) - works

    =SUM(IF(ISTEXT(A:A)*COUNTIF(A:A,A:A)=1,1,0)) - total is zero

    =SUM(IF(ISTEXT(A1:A37)*COUNTIF(A1:A37,A1:A37)=1,1,0)) - total is zero

  31. Dear Sir/Madam

    Please help
    I have following data.
    A1 = Maths/Bio. Gurpreet,Sandeep
    A2 = Physics Kuldeep
    A3 = Economics Priyanka
    A4 = Maths/Physics Gurpreet, Ramesh

    It's a school time table
    As I type the name "Gurpreet" in B1 it shows me duplicate
    I wish to find if some Teacher's name is repeated in Column A

    1. Create three separate columns. First column has the class name, the second has the teacher's name and the third has another teacher's name.
      Separate the teacher's names into two columns.
      Then, in a fourth column enter =COUNTIF(B:B,B1)>1
      The result will show "TRUE" if it is a duplicate.
      If you want to see if the teachers in column C are duplicates, just enter =COUNTIF(C:C,C1)>1 into column D.

  32. DD MM YYYY DD/MM/YYYY
    06 04 2017 06.04.17
    25 04 2017 25.04.17
    23 03 2017 23.03.17
    08 08 2017 08.08.17
    02 09 2017 02.09.17
    30 12 2014 30.12.14
    21 01 2015 21.01.15
    29 03 2015 29.03.15

    I FILL UP SOME DATE OF BIRTH DEFIRANT CELL(25 DAYS 04 MONTH 2017 YEAR) HOW TO MAKE TOGATHER NEXT CELL 25.04.2017 OR 25/04/2017

    PLEASE SOLVE IT.

    1. Hello,

      Please try to solve your task with the help of the Merge Cells tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.
      After you install the product, you will find Merge Cells in the Merge section under the Ablebits Data tab.

      Hope this will help you with your task.

  33. For EX

    we have a location

    DEL-LON
    LON-JFK
    MCO-TPA

    We have to find that DEL or LON or JFK or MCO or TPA are repeating how many times in each row.

    1. Hello, pankaj,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  34. Hi,

    I am wondering if someone could help me?

    I have some data with 2 columns of interest. One column is the case number and the other is number of days the case took to complete. Given the inadequacies of the search tool available, I have multiple duplicates for some of the cases. Not all cases are duplicated, and those that have been duplicated have been duplicated anywhere between 2 and 6 times. With each case duplicate, I have another time value generated. Some of these time values are the same for all duplicates, some are different, and they are not in numerical order.

    What I am trying to do is for each case, to pick out the largest time value.

    The example below might make more sense:

    What I have- What I want-
    CASE TIME CASE TIME
    A 2 A 5
    A 1
    A 5
    B 4 B 4
    C 3 C 3
    C 1
    D 4 D 4
    D 4
    D 4
    E 1 E 1
    F 2 F 6
    F 2
    F 6
    F 2
    F 2
    F 2

    Hopefully someone can get further with it that I have been able to! Thank you in advance!

    1. (sorry the example, but clearer hopefully!)

      What I have-
      CASE TIME
      A 2
      A 1
      A 5
      B 4
      C 3
      C 1
      D 4
      D 4
      D 4
      E 1
      F 2
      F 2
      F 6
      F 2
      F 2
      F 2

      What I want-
      CASE TIME
      A 5
      B 4
      C 3
      D 4
      E 1
      F 6

      1. Hello, Hannah,

        Please try to solve your task with the help of the Consolidate Sheets tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.
        After you install the product, you will find Consolidate Sheets in the Merge section under the Ablebits Data tab. To get the result you need, you should choose the following options on step 2 of the Wizard:
        1. Select the “Max” function to consolidate your data from the drop-down list;
        2. Choose the “Consolidate by label” option and tick both check-boxes next to “Use header label” and “Use left column label”.

        Hope this will help you with your task.

  35. Dear Svetlana.

    I have an issue regarding to duplbicates in Excel.

    Name Number of apples
    Anne 2
    Anne 8
    Anne 5
    Eric 14
    Eric 7

    What I want to do is to sum up all the number of apples given each name.
    So I need the result to be Anne: 15 apples and Eric 21 apples. Do you know how to calculate this? I have used the Sumifs function, but I get this result:

    Name Number of apples Total number of apples each person
    Anne 2 15
    Anne 8 13
    Anne 5 5
    Eric 14 21
    Eric 7 7

    So my question is, how can I use a function that just gives me the sum of number of apples every person without showing the sum of the other rows. So I want the result to be this:

    Name Number of apples Total number of apples each person
    Anne 2 15
    Anne 8
    Anne 5
    Eric 14 21
    Eric 7

    I would appreciate your help.

    Best regard,
    R.R.

    1. Dear Rouzbeh Rasai,
      The reason why you get the wrong result using SUMIFS function is probably that you did not use absolute cell reference.
      Try this formulas:
      =SUMIF(A$2:A$6,"Anne",B$2:B$6)
      =SUMIF(A$2:A$6,"Eric",B$2:B$6)

      As for the result you want to receive, to get it you need to create a VPA macro.

  36. Hi!

    This article seems to be very interesting, but I have noticed that the formula syntax in my MS Excel is different.
    In particular it is:
    =COUNTIF(range; criteria)

    For example:
    To count how many times the value in A1 is repeated in the range A1:A100 the working formula works is:

    =COUNTIF(A1:A100;A1)

    Then, I suppose that the syntaxes are different and it seems to me that they follow diffent criteria.

    In fact, if I used the first formula you indicated, that is

    =COUNTIF(A:A, A2)>1

    in my MS Excel doesn't work.

    Could you help me to understand which criteria I have to use to translate you syntax?

    Thank you so much
    Ivan

    1. Hello, Ivan,
      as you may notice, the arguments are divided by semicolon (;) in your formula, while in ours – by (,) comma. It may happen due to the regional settings for the list separator. Try the formula below and read this topic to find out more.
      =COUNTIF(A:A; A2)>1

  37. Using a formula above, I was able to identify duplicate and unique rows based upon 3 separate columns (Barcode, Custodial Account, OSVer) in my spreadsheet. Now that I have the rows identified, I need a formula that will keep only the unique rows where the Barcode and OSVer are duplicates, but the Custodial Accounts are different based upon the most current date contained in the LastHWScan column. My spreadsheet has 50000 rows of data and will change daily.

    Barcode CustodialAcct OSVer LastHWScan
    315374 11313 10 3/23/2017 0:04
    315374 11313 10 3/17/2017 3:39
    315376 212 10 3/23/2017 18:14
    315376 11376 10 3/17/2017 2:48
    315377 11313 10 3/23/2017 14:27
    315377 11313 10 3/16/2017 11:35
    315381 11313 10 3/23/2017 22:33
    315381 11313 10 3/16/2017 15:49
    315391 11313 10 3/23/2017 9:54
    315391 11313 10 3/16/2017 8:55
    315394 11376 10 3/23/2017 18:42
    315394 11313 10 3/17/2017 2:29
    315396 212 10 3/23/2017 20:38
    315396 11376 10 3/15/2017 14:41

    1. The formula can't change the data in another cell. You can create an additional 'Helper' column and, using the next formula, indicate unique rows (CTRL+Shift+Enter to create an array function):

      =IF(D2=MAX(IF( ($A$2:$A$15=$A2)*($B$2:$B$15=$B2)*($C$2:$C$15=$C2)=1,$D$2:$D$15,0)),"Unique","")

      As a result, you will have something like in the example below. But it will run slow within a large data amount.

  38. WHATS WRONG WITH THIS FORMULA? NOT WORKING? USING EXCEL 2016

    =IF(COUNTIFS($CU2:$CU59862,CU2, $CA2:$CA59862,CA2, $FL2:$FL59862,FL2, $DA2:$DA59862,DA2, $DV2:$DV59862,DV2) >1, "DUPLICATE ROW","")

    1. THIS FORMULA is NOT WORKING?
      But, I want to see that when I put next same number show me 2,3,4,5,6,7, ...........

      Please help me.

  39. Hello,

    I'm wanting to count the number of days worked in a month and ignore the duplicates. For example:

    20/05/2016
    20/05/2016
    21/05/2016
    22/05/2016
    22/05/2016

    The answer should be 3 days because I don't want to count the duplicates.

    1. Try Pivot Table to summarize your workdays. Pivot table is great because it is versatile. Just insert -> pivot table -> make sure the table is selected and then click okay.

    2. hi.. i have the same problem here.. do you have a solve?

  40. I am trying to write a formula in conditional formatting that would highlight duplicate values in a column, but only those duplicates with a value >1. I have several 1's in the column, but do not want those to be highlighted. Is there a way to accomplish highlighting only those duplicate values whose value is >1? Thank you ever so much!

    1. Hi Lisa,

      Assuming your values are in column A, you can create a conditional formatting rule withe one of these formulas, where A2 is the first and A10 is the last cell with data.

      Highlight duplicates including 1st occurrences:
      =AND($A2>1,COUNTIF($A$2:$A$10, $A2)>1)

      Highlight duplicates without 1st occurrences:
      =AND($A2>1, COUNTIF($A$2:$A2, $A2)>1)

      1. Hi Svetlana, thanks so much. I'm afraid I was not clear in my earlier post - I am trying to write a formula in conditional formatting that would highlight duplicate number values in a column, but only those duplicates with a number value >1. Not based on occurrences of the number, but the number value itself.

        My number values in the column range from 1 to 5000, and while there will be many "1"s in the column, I do not want those highlighted. Only the duplicate number values greater than "1" (i.e., 2-5000) do I want to highlight.

        Is there a way to apply a threshold to the highlight duplicates conditional formatting? Thank you again! Lisa

        1. Hello Lisa,

          The formulas do exactly what you describe :)

          The difference is that the second formula does not highlight the 1st occurrence of a duplicate number in a column while the first formula does. 1's are ignored in both cases.

          For example, if you have number 100 in cells A2 and A3, the 1st formula will highlight both cells, while the 2nd only A3. I've created a simple example for your reference and you can download it here.

  41. Hi I'm trying to delete(or minus, source 1 minus source 2) cells, if the cells contain the same information from two different source. For example,
    source 1 source 2 result
    product product product
    a123 a123 b123
    b123 c123 d123
    c123 a123 a123
    a123 a123
    d123 g123
    a123
    a123
    g123

    Is any functions I can use to get the result? (I was thinking to use IF function and VLook to show if there is a match, then filter all the match, delete them. But that won't be reasonable, if some products display more than one times from both sources)

    1. Sorry, format change, example seems awkward

      s 1
      a123
      b123
      c123
      a123
      d123
      a123
      a123
      g123

      s 2
      a123
      c123
      a123

      result
      b123
      d123
      a123
      a123
      g123

  42. Hi,

    Great tutorial. I'm wondering if there is a way for excel to automatically count a duplicate, show the value (number of duplicates) in another cell and then delete the duplicate while still maintaining that value.

    As an example. I'm trying to create a spreadsheet where I enter multiple entries (let say number of packages people have). Like so:

    A B
    Name # Packages
    Alex 1
    Michael 1
    Amy 1
    Alex 1

    As above example, when I entered Alex name again, I want Excel to identify that his name is already in the entry and it will automatically add 1 to the first occurrence of Alex and will remove the Alex entry I just made.

    I know this can be done in combination with Conditional Formatting where I can change the color of the cell if there is a duplicate. But I wish to make it even better so I could do that automatically.

    Basically the algorithm would be:
    1.Excel sees there's a duplicate.
    2.Plus 1 to the column B of the first occurrence.
    3.Delete the duplicate while still maintaining the value of the B column in the first occurrence.
    4.Repeat the process if there is more duplicate entry.

    So, this is basically a live iteration where Excel is counting and removing while I'm still in the process of entering data. Not just at the end when I'm done with it.

    I hope someone can help.
    Thanks

    1. Sorry for the messed up example. I don'n how to post a screenshot here in the comment. But the example was Column A (Name) and Column B (#Packages)

  43. Amit,

    The formula was wrong. Try this one, it will work.

    =IF(COUNTIF($A$2:$A2, A2)>1,"Duplicate","")

    1. Dear Bekim,

      Your formula identifies duplicates without 1st occurrences. The formula mentioned by Amit identifies duplicates in cells A2:A8 including their 1st occurrences. Both formulas are correct, and which one to use depends on a particular task. The following screenshot shows the difference:

      Duplicates with 1st occurrences vs. duplicates without 1st occurrences

      1. Hi,

        I need a count of Unique & Duplicate value as 1.
        In above formula it counts only duplicates
        Receipt Count
        2001
        2001 1
        2002 1
        2003
        2003 1
        2004 1

  44. The find duplicates formula doesn't work for me.

    I have used exactly what you have listed on this page:

    =IF(COUNTIF($A$2:$A$8, $A2)>1, "Duplicate", "")

    with the exact criteria, but it highlights all cells as duplicates, I have changed the copy series drop down to fill and it doesn't work.

    I would really like to get it working, as its really bugging me and also is a very useful formula to know.

    thanks
    Amit

    1. Dear Amit,

      The formula is correct. Just be sure to properly adjust the cell references in the formula, where A2 is first and A8 is the last cell of the range that you want to check for duplicates.

      If you have a variable range, you can use the column reference like this:

      =IF(COUNTIF(A:A, $A2)>1, "Duplicate", "")

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)