How can I create a table using ARRAYFORMULA that concatenates all items meeting the same criteria in one cell?
I've been trying for the life of me to figure out how to do this and cannot.
I have a table: ColA is items, ColB is their category.
| Item | Category |
|---|---|
| Apple | Fruit |
| Orange | Fruit |
| Cauliflower | Vegetable |
| Rose | Flower |
| Tulip | Flower |
| Steak | Meat |
| Carrot | Vegetable |
| Salmon | Meat |
I'd like to make a new table that has ColD (Categories) and ColE (all the items that belong in that category).
| Category | Items |
|---|---|
| Fruit | Apple, Orange |
| Vegetable | Cauliflower, Carrot |
| Meat | Steak, Salmon |
| Flower | Rose, Tulip |
Currently, I can do that with this formula:
=ARRAYFORMULA(CONCATENATE(REPT(A3:A&" ",B3:B=G3)))
The problem is I need this to be written as an ArrayFormula. CONCATENATE (and other alternatives like FILTER, JOIN) don't work.
Any help would be so appreciated, thank you!