Count Unique Duplicates in Two Columns

[dwgnome]

Hi,

I need a one formula solution to compare two columns for duplicates and count the number of matched pairs. Need to ingore blanks. Can have matched numbers or letters. Asterik denotes a blank (empty) cell. Also only consider the first matched pair if there are more than one matched pairs.

Col A Col B
a
b a
* b
c c
d y
x f
f z
f z

In this case I only want to consider a:a, b:b, c:c and f:f, resulting in the count of 4.

Any help would be appreciated

 

[dwgnome]

Thank you Domenic for stepping in to save the day. Your solution works perfectly!

I must say, I don't quite understand what the formula is doing and why one array has ISNUMBER in front while the other does not. What does this accomplish? While it works well, your original solution is a bit more transportable. Meaning don't have to setup Named Ranges in different workbooks etc.

Thank you both for contributing to the success of Excel users everywhere!

 

[Domenic]

Thank you Domenic...

You're very welcome!

While it works well, your original solution is a bit more transportable. Meaning don't have to setup Named Ranges in different workbooks etc.

If you're using Excel 2007, there's no need to set-up the named ranges. Use the references instead.