Google sheet array formula with Filter & Join

Question

I need to build a table based on the following data:

Ref	Product
R1	ProdA
R2	ProdC
R1	ProdB
R3	ProdA
R4	ProdC

And here the result I need:

My Product	All Ref
ProdA	R1#R3
ProdC	R2#R4

The particularity is that the 'My Product' column is computed elsewhere. So I need an arrayformula based on 'My Product' column to look in the first table to build the 'All Ref' column. You follow me?

I know that Arrayformula is not compatible with filter and join ... I expect a solution like this one Google sheet array formula + Join + Filter but not sure to understand all steps and if really adapted to my case study.

Hope you can help.

Nabnub Nabnub · Accepted Answer · 2021-03-25T20:38:36

You could try something like this:

CREDIT: player0 for the method shared to similar questions

=ARRAYFORMULA(substitute(REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(
 QUERY(QUERY({B2:B&"😊", A2:A&"#"}, 
 "select max(Col2) 
  where Col1 !='' 
  group by Col2 
  pivot Col1"),,999^99)), "😊")), "#$", )," ",""))

Step by step:

Google sheet array formula with Filter & Join

1 Answers