I'm have a list of IDs in a column on Google Spreadsheets. I need to check:

  1. That there are no duplicates in the column

  2. Which cells contain the duplicates

Is it possible to do this?


A closely related question is How can I find duplicates in a column, using formulas? but your desired output is somewhat different (you want to see the cells that contain duplicates rather than just the values themselves).

  1. The formula =counta(A1:A)-countunique(A1:A) evaluates to zero if all entries in A are unique; otherwise it shows how many duplicates are there.

  2. To see which cells contain duplicate IDs, apply conditional formatting to column A1:A with the custom formula =countif(A$1:A, A1)>1 (If the range was instead B3:B, the formula would be =countif(B$3:B, B3)>1).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.