Thanks, Bo_Ry!
Having looked more closely at the dummy data that I pasted in Table 2, I now see that I made an error: The values in range S8:S11 should have been moved to R8:R11 when I updated the file with the true/false criteria and removed some values for rows that I marked "true".
I therefore tried to come up with a solution myself, and think I have found one. Unfortunately the IF statement sometimes produces and array of values that starts with FALSE, which doesn't work well with the AGGREGATE function. I therefore have to multiply with 1 to turn it into a zero, but that creates a problem with #SPILL! errors in cases where the array doesn't start with FALSE. To handle this I found out that I could use the SWITCH function to turn zero into one if the whole row_num expression in the INDEX formula turns out to return zero. Long story short; the formula that started out quite neat, suddenly turned out to be quite large. Perhaps some of you would like to have a look at it and see if it can be improved?
I also looked at Bo_Ry's formula in cell L3, and it doesn't remove Code values which isn't being used by phase numbers that occur in Table 2. Could this be solved?
Here is the formula I wrote in cell L4 (without the SWITCH statement):
And here it is, including the SWITCH statement:
Best regards,
Marbleking
Bookmarks