• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shorter & Efficient Formula: Unique Count of Matching values between Two Variable length Lists

Asheesh

Excel Ninja
Hi All,

I am sure this one is easy for you guys here; however, the intention is to check if there is a shorter and a better way of doing this with the help of excel formulae only

One of my colleagues approached me this morning and asked if there is any formula approach to get the unique count of matches between two lists. Though I came up with a solution which is 89 characters long without an equal “=” sign, I still think it can be improvised.

Limitations:

  1. Lists may or may not have blanks

  2. Lists may contain numbers or text values

  3. Both ranges may or may not be equal

  4. No named ranges to be used.

  5. No helper cells please
See the attached for sample data.

As per the attached, the result should be 5.

Unique matches between the two lists are Dashboard, Cursor, 55, 7986 & 21
 

Attachments

Last edited:

Luke M

Excel Ninja
Looks like a good one. I take it that if both lists have blanks, we shouldn't count that as a match?
 

Luke M

Excel Ninja
Here's what I came up with. Clocks in at 66 characters if you don't count the array.

=SUM(IFERROR((COUNTIF(B2:B17,A2:A17)>0)/COUNTIF(A2:A17,A2:A17),0))

Note, needs to be confirmed as an array.

I'm not completely satisfied with the IFERROR function needing to be in there to filter out the div/0 problem. I forgot that COUNTIF won't work on blank cells.
 

Asheesh

Excel Ninja
Hi,

Nice ones and considerably very short.

Modified my initial approach by removing an extra formula and I am sitting at 65 characters long formula which is

SUM(--(FREQUENCY(IFERROR(MATCH(A2:A17,B2:B17,0),""),ROW(A:A))>0))

Results on Efficiency part on the real time data set..

1) SUM(IFERROR((COUNTIF(B2:B17,A2:A17)>0)/COUNTIF(A2:A17,A2:A17),0)) takes 9.5 seconds

2) SUM(((COUNTIF(List2,List1)>0)/COUNTIF(List1,List1&""))) takes 7.2 seconds

3) SUM(--(FREQUENCY(IFERROR(MATCH(A2:A17,B2:B17,0),""),ROW(A:A))>0)) takes 1.8 seconds

Can you suggest any alternate to IFERROR..
 
Top