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.
Or create s pivoot table to count name and sort by count high to low.
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
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)
Thanks Angus – yes UNIQUE offers lots of solutions.