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

• 7.8 KB Views: 50
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?

Asheesh

Excel Ninja
Absolutely right..

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 and NARAYANK991

NARAYANK991

Excel Ninja
Hi Luke ,

Have you tried this :

=SUM(((COUNTIF(List2,List1)>0)/COUNTIF(List1,List1&"")))

Narayan

• r1c1, Asheesh and Luke M

Luke M

Excel Ninja
Hi Luke ,

Have you tried this :

=SUM(((COUNTIF(List2,List1)>0)/COUNTIF(List1,List1&"")))

Narayan
Perfect, I knew there had to be a way. Nice job of forcing XL to convert to strings.

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

Deepak

Excel Ninja
Check this...

=COUNTA(FREQUENCY(A2:A17,B2:B17))

NARAYANK991

Excel Ninja
Hi Deepak ,

I think you should check your formula using different data sets.

Narayan

Deepak

Excel Ninja
Oh! By bad, apologies for the same. 