Import multiple data on one cell

Question

Hi I have data on columns A, B, C, D on Google sheet

I added the following formula on H2 which works in importing columns C & D as wished.:

 =UNIQUE(filter(C2:D,A2:A<>""))

I tried inserting this formula in J2 but it doesn't handle duplicates:

=ARRAYFORMULA(IF(H2:H=C2:C,B2:B))

My goal is to get the results written manually in G columns on this snip and keep the previous formulas. It should write the name of door twice if necessary separated by a comma or write the different doors in the same cell adjacent to the name.

Please help

Answer 1

use:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(
 QUERY({A2:A&",", B2:B&"×", ROW(B2:B)}, 
 "select max(Col1) where Col1 <> ',' group by Col3 pivot Col2"),,9^9)), "×")), ",$", ))