Two lists with some names the same; can it count names that appear more than once as only one?
Posted by Merle Mason on January 23, 2001 11:53 AM
I have two lists of names; some of the names appear in both lists. Is there a way to do a COUNTA and have it only count names once? That is, if I have two lists of ten names, and five of the names are the same, is there a way to get it to return a count of 15? Thanks.
Posted by Ian on January 23, 2001 12:56 PM
There must be a better way, but here is one that should work. Assume your first list in A1..A10 (sorted) and second list is B1..B10. Do =Counta at the bottom of each for a total of 20. Then subtract duplicates by using vlookup in columns c with the following
=IF(ISNA(VLOOKUP(B1,$A$1:$A$10,1,FALSE)),0,1)
Total this and your unique records will be A+B-C. Good luck.
Posted by Mark W. on January 23, 2001 1:52 PM
Suppose A1:A10 contains {"Sally";"Mary";"Larry";"Jerry";"Linda";"Mike";"Jim";"Jake";"Moe";"John"}
and B1:B10 contains {"Mark";"Sally";"Barry";"Larry";"Peter";"Tom";"Paul";"Mary";"Moe";"Jim"}.
Then {=COUNTA(A1:A10,B1:B10)-SUM((A1:A10=TRANSPOSE(B1:B10))+0)} does what you
want. This is an array formula which must be entered using Shift+Ctrl+Enter.
Posted by Merle Mason on January 23, 2001 3:17 PM
That worked...Thanks!
Posted by Merle Mason on January 23, 2001 5:12 PM
Hi Mark,
That is unbelievable! Works like a charm. Thanks so much!
