Confirming Names are Unique in Excel

COUNTIFS to the rescue

If you have a list of first names and last names and you want to make sure the list has no duplicates you can use a formula to confirm the names are unique.

Hat tip to Wyn Hopkins from Access Analytic for reminding me how useful COUNTIFS is.

Our list is below.

Note the first names are duplicated, but the combinations of first and last names are not duplicated.

Subscription version formula

The latest subscription version formula in cell D1 is

=SUM((COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9)=1)*1)=COUNTA(A2:A9)

This returns TRUE if the list is unique. FALSE if there are any duplicates.

All versions formula

The formula in cell D2 is

=SUMPRODUCT((COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9)=1)*1)=COUNTA(A2:A9)

These both work the same way.

The COUNTIFS functions have a range in the range argument and the criteria argument. This means 8 separate COUNTIFS calculations are performed (one for each row). The 8 results compared to 1. Any duplicates will return more than 1 and hence will return FALSE. FALSE = zero and when multiplied by 1 will return zero. In practice this means the FALSE (duplicate) rows won’t be counted.

The COUNTA function counts all entries. When the number returned from the SUM or SUMPRODUCT is less than the COUNTA result then FALSE will be displayed. This means there are duplicate entries.

The image below shows the F9 key being used to calculate different parts of the formulas to see their results.

The image below has the unique list. Below that there is a duplicate name in the list in row 6.

 

This solution assumes everyone has a first name as the COUNTA counts column A.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 thoughts on “Confirming Names are Unique in Excel

    • Thanks Winston
      Pivots are great and I use them all the time, but not sure a pivot is the best solution if you want a cell to say whether there are duplicates in the list.
      Regards
      Neale

  1. Another way to do this in modern excel:
    COUNTA(UNIQUE(A2:A9&B2:B9))=COUNTA(CONCATENATE(A2:A9&B2:B9))

    Or like this if your confident column A won’t have any blank values:
    COUNTA(UNIQUE(A2:A9&B2:B9))=COUNTA(A2:A9)