My question is very, very similar to this one:
However the answer given there is not enough for my situation (and my reputation is too low to comment there); I need the result to be a spilling range.
Here’s the input data:
| col A | col B |
|---|---|
| fruits | apple |
| fruits | banana |
| fruits | lemon |
| fruits | mango |
| fruits | orange |
| veggies | carrots |
| veggies | potatoes |
However unlike the aforementioned question, here both input columns are spilled ranges.
We also know that both columns have the same number of rows.
The expected output is:
| col C | col D |
|---|---|
| fruits | apple, banana, lemon, mango, orange |
| veggies | carrots, potatoes |
The solution that works for column C (because it does spill) can be =UNIQUE(FILTER(A:A,A:A<>"")).
However, unlike the aforementioned answer, I can’t have the formula copied over the entire D column, because the number of resulting rows is unknown. I need it to be a spilling range.
I seem to be using Office 2021.
