Advanced Formula Challenge #4: Missing Values 6

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:

Picture1

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:

=IF(ROWS($1:1)>$D$1,"",

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!

6 comments

  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:

    =COUNT(list1)-COUNT(list2)

    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:

    =SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
  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!

    Regards

  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.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s