In the following sheet I am trying to grab a list of GUID keys from tab sheet1
and concatenate them to their respective order in tab sheet2. My expected output is in tab sheet2 column B as follows:
Example: Order1 and Order3 both have a 2 in their columns so the resulting output for 2 on tab sheet2 should be both GUID keys bbb and ccc. As is shown in tab sheet2 column B
I used the following filter to get the unique order numbers in tab sheet2 column A.
=sort(unique(transpose(split(arrayformula(concatenate
(if(len(Sheet1!A2:C)>0,Sheet1!A2:C&";",""))),";"))))
I tried to use the following
=CONCATENATE (FILTER(Sheet1!D2:D, Sheet1!A2:C=Sheet2!A2 ))
but it just errors out saying:
FILTER range must be a single row or a single column
So is there a way to write this in order to get the range and concatenate the results? I also played around with some app script to make it work but that couldn't do the trick and it seems like a simple sheets function would do what I need.
This relates to the following but its the inverse of that function more or less.