How do I take 2 columns, one for name and one for values, and spit out the unique number of amounts per unique name? Then make a list

Question

I was given a list of property names and the number of bedrooms they offer, but the info was given like this: 0 value = studio

Property	Bedrooms
Name1	1
Name1	2
Name1	3
Name2	0
Name2	1
Name2	4
Name3	1
Name3	2

I'm hoping to eventually get this information to spit out into a new sheet like so:

Property	Bedrooms
Name1	1, 2, and 3 bedrooms
Name2	studio, 1, and 4 bedrooms
Name3	1 and 2 bedrooms

I can get the numbers to populate in a new cell along with the word "bedroom" by using the join function, but I'm not finding much success getting conditional values to work for things like adding a comma after the second value if there's a third.

Any help, even just linking me to the right threads, videos, etc. would be greatly appreciated!

Answer 1

try:

=ARRAYFORMULA(SUBSTITUTE(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY(
 {A2:A&"×", SUBSTITUTE(B2:B, 0, "0studio")&","}, 
 "select max(Col2) 
  where not Col1 starts with '×' 
  group by Col2
  pivot Col1"),,9^9)), "×")), ", (\d+),$", " and $1 bedrooms"), 
 "0studio", "studio"))

---

update: