Let’s say I have the following two differently sized arrays in Excel:
| array_1 | array_2 |
|---|---|
| A | A |
| B | C |
| D | E |
| E |
I want to write a function COMPARE(array_1, array_2) where this returns (as an array) all of the items in array_1 that are not in array_2; so in this case COMPARE(array_1, array_2) should equal {B, D}. COMPARE(array_2, array_1) should equal {C}.
Now, this is easy to do if you have both arrays “on the board”, as it were. Suppose array_1 is A2:A5, while array_2 is B2:B4. In that case, COMPARE(array_1, array_2) = FILTER(array_1,NOT(COUNTIF(array_2, array_1))). COUNTIF works as a dynamic array function and gives an array the same dimensions as array_1.
Here is the problem: I can’t get this to work when the arrays are not directly “on the board”. That is, if the arguments of my COMPARE function are themselves values of dynamic array functions, the COMPARE function breaks.
The specific use case I have in mind involves three tables. Here are dummy versions:
t_1
| item_X | item_Y | COMPARE_XY |
|---|---|---|
| ABC | 123 |
t_2
| item_X | customer |
|---|---|
| ABC | Alpha |
| ABC | Beta |
| ABC | Delta |
| ABC | Epsilon |
| DEF | Alpha |
| … | … |
t_3
| item_Y | customer |
|---|---|
| 123 | Alpha |
| 123 | Kappa |
| 123 | Epsilon |
| 456 | Alpha |
| … | … |
I want to write the COMPARE function in the blank cell on t_1. I want ABC to pull {Alpha, Beta, Delta, Epsilon} from t_2 and 123 to pull {Alpha, Kappa, Epsilon} from t_3. Then I want it to compare these arrays as discussed above. Pulling these arrays from t_2 and t_3 is easy: it’s just another FILTER function. But when I combine this technique with the version of the COMPARE function I sketched above, it gives me errors. Specifically, the COUNTIF function seems to give errors if its arguments are arrays supplied by dynamic array functions.
I hope that someone is still with me. Does anyone know how to accomplish this task?
