Google-sheets – Concatenating cell values to a matching cell

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-querypivot

In the following sheet I am trying to grab a list of GUID keys from tab sheet1

enter image description here

and concatenate them to their respective order in tab sheet2. My expected output is in tab sheet2 column B as follows:

enter image description here

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.

Best Answer

=ARRAYFORMULA({SORT(TO_TEXT(UNIQUE(FILTER(
 {Sheet1!A2:A; Sheet1!B2:B; Sheet1!C2:C}, 
 {Sheet1!A2:A; Sheet1!B2:B; Sheet1!C2:C}<>"")))),
 TRIM(TRANSPOSE(QUERY(TRANSPOSE(QUERY(IF(
 QUERY(TO_TEXT(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(
 QUERY(TRANSPOSE(IF(LEN(Sheet1!A2:C), "♠"&Sheet1!A2:C&"♦"&Sheet1!D2:D, ))
 ,,999^99)),,999^99), "♠")),"♦")), 
 "select Col1,count(Col1) group by Col1 pivot Col2 order by Col1", 0)<>"", INDEX(
 QUERY(TO_TEXT(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(
 QUERY(TRANSPOSE(IF(LEN(Sheet1!A2:C), "♠"&Sheet1!A2:C&"♦"&Sheet1!D2:D, ))
 ,,999^99)),,999^99), "♠")),"♦")), 
 "select Col1,count(Col1) group by Col1 pivot Col2 order by Col1", 0), 1, ), ), 
 "offset 1", 0)),,999^99)))})

0

Related Topic