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
This returns TRUE if the list is unique. FALSE if there are any duplicates.
All versions formula
The formula in cell D2 is
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.