Imagine two lists of names. We have three questions: (1)how many unique names in List 1? (2)how many unique names in List 2? (3)how many unique names from List 1 are found in List 2? 

(download my Demo Excel File)

 

The Lists

List 1 has 35 names (16 unique names)

List 2 has 54 names (18 unique names)

 

 

 

 

 

 

 

 

Three Questions

(1)how many unique names in List 1?  (2)how many unique names in List 2?

This formula counts unique names:

=SUMPRODUCT(1/COUNTIFS(G7:G41,G7:G41))

Question 3 is tricky: (3)how many unique names from List 1 are found in List 2?

 

 

What Kind of Solution?

How should we solve Question 3?

  • quick and simple?
  • power query?
  • single array formula?
  • helper formulas?

Each solution has pros and cons. Let’s explore each one.

 

quick and simple

Why complicate things?

Copy paste List 1 names into column A. Use Remove Duplicates feature. Match function tells us if name is found in List 2. Count function provides final answer.

You would have to repeat Remove Duplicates step if the data changes.

 

 

 

 

 

 

 

 

 

 

power query

All work is done inside Power Query. Great for a large datasets that change frequently. Power Query does have a learning curve but it’s an amazing tool.

Summary: load each list, remove duplicates from List1, merge List 1 & List 2. Export back to your sheet.

 

 

 

 

 

single array formula

If the answer must be dynamic (power query requires a refresh) and fit in a single cell consider this:

To enter this formula hold keys ‘Ctrl’ & ‘Shift’ & ‘Enter’.

 

 

Overview explanation:

The inner MATCH is: MATCH(G7:G41,G7:G41,0) It looks for all List 1 names inside List 1! 

Compare inner results with counter values to replace duplicates with blanks IF(MATCH(G7:G41,G7:G41,0)=ROW(G7:G41)-ROW(G7)+1,G7:G41,””)

Now the outer MATCH’s lookup_value contains unique List 1 names. We look for these in List 2 and COUNT gives us the total found.

 

 

helper formulas

If you want a live dynamic answer but not the complexity of solution 3 then consider this.

Column F has a simple counter, column I has this simple formula:

=IF(G7=””,”^^^^^”,IF(MATCH(G7,$G$7:$G$41,0)=F7,G7,”^^^”))

Use either formula below, that look at column I helper, to get the final count:

=SUMPRODUCT(–ISNUMBER(MATCH($I$7:$I$60,$J$7:$J$60,0)))

or this array formula (requires Control Shift Enter….not just Enter)

=COUNT(MATCH($I$7:$I$60,$J$7:$J$60,0))

 

 

What’s Your Favorite Solution?

The quick and simple solution is great for the masses especially if it’s a one time question.

I’m becoming comfortable with Power Query so that solution was easy. It’s an amazing tool but let’s not forget that a Power Query solution is not fully dynamic. It requires a refresh. If building a multiple step model on top of the answer you would probably prefer one of the formula solutions.

Array? I love it but it is challenging for most Excel users to understand. This solution was the most fun to build as I had to be creative.

Helper formulas. Splitting the array logic out into steps makes it easier to audit for non array fanatics.

 

What about you? What solution do you prefer? Do you have a different solution?

 

Robert H Gascon’s Idea!

See Robert’s comment below. My ‘single array formula‘ solution is difficult to understand/audit. Robert suggests splitting the logic into two parts and creating a named range out of each part! It’s easier to audit this way and the final formula =UniqueCount doesn’t require Control Shift Enter.

I’ve updated my Excel file (see top of post). Go to Formulas/Name Manager. You’ll see named range ‘Unique1’. This is the lookup_value for the outer MATCH function. Named range ‘UniqueCount’ references ‘Unique1’. In cell M5 we just need to type this: =UniqueCount

Another benefit is that with careful planning you could reference the same base named range logic several times! It reminds me of writing database queries where various subsequent queries refer to the same base query. Thanks Robert for the idea!!!

 

About Me

 

My name is Kevin Lehrbass. I live in Markham Ontario Canada. I’m a Data Analyst.

How much of an Excel fanatic am I? I used VBA to create random Mondrian style art! (read this post)

 

3 Comments

  1. Robert H. Gascon says:

    Hello Kevin,
    Your single array formula solution is marvelous! Nonetheless, it looks cryptic to the uninitiated. To make it elegant and avoid Ctrl+Shift+Enter, I named the lookup_value argument of the outer MATCH as “Unique1”. Thus, the formula now becomes:
    =COUNT(
    MATCH(Unique1,
    $J$7:$J$60,0))
    Even so, the formula still requires Ctrl+Shift+Enter. To avoid it, I named the foregoing formula as “CountUniques”. Thus, the formula has been reduced to this, without Ctrl+Shift+Enter:
    =CountUniques
    The reason why the original formula requires Ctrl+Shift+Enter is that the lookup_value argument of the outer MATCH is returned by IF. To avoid Ctrl+Shift+Enter, the entire formula must be defined as a named formula, such that “CountUniques” may also refer to the entire formula without the need of defining “Unique1”.
    I was contemplating another formula that returns the same answer but I unfortunately failed. Defining the entire formula as “CountUniques” was the most I can do to make it elegant and avoid Ctrl+Shift+Enter.
    Cheers.
    Robert

    1. Kevin Lehrbass says:

      Hi Robert,
      One of the benefits of blogging is that I get to learn from people like you! I really like your idea of splitting the logic into named ranges. …Yes, my array definitely does have that cryptic look.to it. I’m going to implement your idea and then update my post! Thanks again for reading and giving your input Robert!
      Cheers,
      Kevin

Post a comment