1

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.

  • not rly clear what exactly are you after. can you elaborate more and add an example of desired output? – user0 May 30 at 14:41
  • The desired output is in link (first sentence) on sheet 2 column B, I'll add a picture as well – littlecoder May 30 at 14:47
1

=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

  • 1
    Wow! I would have never thought of doing it that way, nice! – littlecoder May 30 at 16:30
  • I notice it does not accept hyphens, for example. column A has the value 20-20 but it would return the error "Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 6. Actual: 5." Is there a way to further allow special characters? – littlecoder May 30 at 18:01
  • @littlecoder fixed. answer updated – user0 May 30 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.