# Array Matrix - Compare 2 lists - Sum Unique Values

#### nbrcrunch

##### Well-known Member
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"}

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi nbrcrunch,

I think the following formula can get the same results

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

Regards
Bosco

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.

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.)

nbrcrunch said:
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...

Replies
3
Views
486
Replies
17
Views
1K
Replies
7
Views
588
Replies
8
Views
3K
Replies
5
Views
24K

1,203,171
Messages
6,053,885
Members
444,692
Latest member
Queendom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back