Import multiple data on one cell-CodePudding

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

CodePudding user response：

This can be achieved with the formula below

=join(", ",filter($A$2:$A, $B$2:$B=D2))

Unfortunately, this formula is not an arrayformula, and I am not completely certain how to turn it into one (if there is a way). Because of that, this formula will need to be dragged down the column you place it in.

Here is an example of it working in the test sheet I made:

I will continue to try to make this usable with an array formula, and will edit this answer if I am able to find a solution.

Hope this helps! Let me know if you have any questions.

CodePudding user response：

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)), "×")), ",$", ))