How can I create a table using ARRAYFORMULA that concatenates all items meeting the same criteria in one cell?

Question

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!

You wrote "I need this to be written as an ArrayFormula", but then provided an example that is written as an ArrayFormula. Was that a mistake? Or is there something else you need written as an AF? — John Feminella
– John Feminella, Commented yesterday
Ooh to clarify, the ARRAYFORMULA in the code I provided is running an array on the CONCATENATE/REPT to get an output in that single cell. But I would need a formula that doesn't require me to copy it into every cell in the column. — coltzans
– coltzans, Commented 16 hours ago

Martín · Accepted Answer · 2025-11-21 02:56:19Z

1

You can try with a formula like this one:


=BYROW(TOCOL(UNIQUE(B3:B),1),LAMBDA(category,
HSTACK(category,  TEXTJOIN(", ",0,      FILTER(A3:A,B3:B=category)))))

With TOCOL(UNIQUE(B3:B),1) you grab the categories, with BYROW you loop through them, and then stack the category with the filtered values joined by ", " separator

answered yesterday

Martín

10.4k2 gold badges7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

coltzans 16 hours ago

AMAZING THANK YOU! This was exactly what I needed.

Collectives™ on Stack Overflow

How can I create a table using ARRAYFORMULA that concatenates all items meeting the same criteria in one cell?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related