Advanced Formula Challenge #4: Missing Values 7

The challenge this week is as follows: given the two lists of numbers in A2:A12 and B2:B12, generate the list as per the Results column, beginning in C2:


A single formula should be placed in C2 such that, when copied down an arbitrary number of rows, generates the results as above.

Blanks should be returned by the formula in rows beyond the expected number of returns. What’s more, this is to be done via reference to the value in cell D1, also to be derived, and whose value is equal to the total expected number of non-blank returns in column C.

As such, the formula in C2 is to begin with:


What’s more, and perhaps more importantly, any solution is not allowed to consist of formulas which reference the results of formulas in previous cells. By this I mean that the formula in C3 cannot reference cell C2, the formula in C4 cannot reference either cell C2 or cell C3, etc.

The logic and conditions defining the returns are as follows:

  • The number of entries in column B is less than or equal to the number of entries in column A
  • Each of the column B entries is also found somewhere in column A
  • There are never any blank entries in between the entries in either column A or column B
  • The results in column C are to consist of all the values remaining after removing one occurrence of each of the values in column B from the list in column A
  • To put it another way, the combined list from columns B and C is to match precisely that in column A

Also note that, as usual, the choice of range (A2:B12) was here made purely for display purposes and solvers should not think that it is fixed at such: theoretically, any solution should work equally well for a data range of e.g. A2:B1000.

Solution next week. Best of luck!


  1. Not sure if this is what you want or not, but you do get the proper results with advanced filter using list1 as list range, list2 as criteria, copy to as c2, and unique records only checked.

    Also for the count you can use:


    because they are all numbers and you told us list2 was included in list1.

    However if you add extra numbers in list2 then a better formula would be:

  2. Hi and welcome to the site!

    Actually that’s not a method I’d considered at all, mainly because I set these challenges with the idea that they be solved using worksheet formulas alone.

    I’m aware that there are often more efficient methods (e.g. VBA, helper-column set-ups, in-built features such as Remove Duplicates, Advanced Filter, etc.) to solve the task in hand, but I guess I’m interested in seeing what can be achieved using just worksheet functions, which I genuinely believe is more than most people realise.

    Still, yours is certainly the correct, practical choice. If it works, that is. Perhaps I’m not doing it correctly, but when I follow your instructions I get a result of (from C2 down): 1, 1, 2, 3, 4, 5, 6 which is obviously not correct.

    Both your count formulas are spot on though, so well done!

    Thanks again for your contribution and hope to hear more from you!


  3. Not sure what I was smoking yesterday but you are correct, Advanced Filter didn’t work. I must have been dyslectic yesterday. (LOL! 🙂 – XOR)

    I know you were going for a formula solution but I had to throw in the towel. My head started spinning to much so I had to give up. I’m probably gonna kick myself when I see the solution and realize just how close I really was. Thanks again.

  4. I realise this is ancient history but before I look at your solution this is what I came up with:

    Define the named formula ‘freq’ to refer to


    where ‘k’ is a suitably sized row counter [at least MAX(list1,list2)].

    Generate the results across multiple array columns where the named formula ‘results’ refers to


    and 's' is a suitable sized column counter [at least MAX(freq)].

    List the results to a single column using SMALL in a worksheet formula


    where 'n' refers to


    In a way, I have failed the test because I usually use array formulas, which cannot be filled down cell by cell. I think my avoidance of all direct cell references is OK.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.