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.

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

1 Answer 1

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

3
  • 1
    Wow! I would have never thought of doing it that way, nice! May 30, 2019 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? May 30, 2019 at 18:01
  • @littlecoder fixed. answer updated
    – user0
    May 30, 2019 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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