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!