Array Matrix - Compare 2 lists - Sum Unique Values

[nbrcrunch]

I thought this was such an ingenious formula by a "Mark W" that I wanted to resurrect it. I found it on the purchased copy of Mr. Excel forum postings (KnowledgeBase vol1)

Scenario:
You have 2 lists of names, both of equal dimensions (though not necessary). Both lists have unique names not appearing in the other list and both lists have duplicated names, both within themselves and in the other. You want a count of unique names.

Setup:
A1:A10 contains the first list of names
B1:B10 contains the 2nd list of names

Solution
=COUNTA(A1:A10,B1:B10)-SUM((A1:A10=TRANSPOSE(B1:B10))+0)
Enter with Ctrl-Shift-Enter

The first part, counta(A1:A10,B1:B10) is simple, it counts 20 entries.

The next part is what is so facinating. Instead of creating a manually coded matrix of List1 running down a column and List2 running across a heading row, it sets this whole matrix up in memory and then performs the matches. In the sample data I had there were 5 such matches.

Finally the simple math is done: 20-5 = 15 unique entries.

Here is the original sample data:
Suppose A1:A10 contains {"Sally";"Mary";"Larry";"Jerry";"Linda";"Mike";"Jim";"Jake";"Moe";"John"}

B1:B10 contains {"Mark";"Sally";"Barry";"Larry";"Peter";"Tom";"Paul";"Mary";"Moe";"Jim"}

 

[bosco_yip]

Hi nbrcrunch,

I think the following formula can get the same results

=SUMPRODUCT((A1:B10<>"")*(1/COUNTIF(A1:B10,A1:B10&"")))

Regards
Bosco

 

[Domenic]

Also, unless I'm misunderstanding the scenario outlined, the first formula will return an incorrect result when A1:A10 contains the following values...

{"Sally";"Mary";"Larry";"Moe";"Linda";"Mike";"Jim";"Jake";"Moe";"John"}

...and B1:B10 contains...

{"Mark";"Sally";"Barry";"Larry";"Moe";"Tom";"Paul";"Mary";"Moe";"Jim"}

The first formula returns 12, whereas the second formula returns 13, which I believe is the correct result.

 

[nbrcrunch]

The first formula is an array formula and correctly returns the result of 15. So does the second formula.

I was wrong, however, in assuming that there could be conditions where the matrix was not the same number of cells deep as it was wide. Conceptually and in reality this can done manually, but apparently not with arrays. From what little testing I did, arrays in formulas such as the original post must always be the same size. If some one can prove differently I would be overjoyed to find out how.

(I was thinking that since the transpose caused the second list to become the header of a matrix (two-way lookup) that it wouldn't matter that it was not the same size as listA.)

 

[Domenic]

The first formula is an array formula and correctly returns the result of 15. So does the second formula.

Yes, both formulas return the correct result for the data that you've given. But if we change the data, as I've shown previously, so that 'Moe' appears twice in List A and twice in List B, the first formula incorrectly returns 12, whereas the second formula returns 13.

(I was thinking that since the transpose caused the second list to become the header of a matrix (two-way lookup) that it wouldn't matter that it was not the same size as listA.)

You could have something like this...

=COUNTA(A1:A10,B1:B15)-SUM((A1:A10=TRANSPOSE(B1:B15))+0)

...but the same problem would exist...