Is there a way to use arrayformula in google sheet to join text with conditions?

userBRuS 0
Viewed 49

Is there a way to get the result column in the picture below? All i want to do is text join the Col1 if the corresponding Col2 belongs to the same groups (E.G. 1,2,3....). Reminded that I want to use arrayformula instead of dragging down the "normal" formula myself everytime.

enter image description here

conditional-statements google-sheets
3 Answers

Use this formula
Or Make a copy of this example sheet.

=ArrayFormula({"Result";
                IF(A2:A="",,
                BYROW(B2:B,
                LAMBDA(v,JOIN(", ",FILTER(A2:A,B2:B=v)))))})

enter image description here

Osm0

Great news for google-sheet lovers that google releases new lambda formulas. You can use BYROW() function to make it spill array. Try below formula.

=BYROW(C3:C9,LAMBDA(x,JOIN(",",FILTER(B3:B9,C3:C9=x))))

To refer entire column use FILTER() function for BYROW().

=BYROW(FILTER(C3:C,C3:C<>""),LAMBDA(x,JOIN(",",FILTER(B3:B,C3:C=x))))

enter image description here

Harun24hr0

Suppose my range of data from B3:C9, want to group the result according the the Column C (or Col2) Here is the formula i googled without using the Lambda function

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(FLATTEN(  QUERY(QUERY({ROW(C3:C9), C3:C9&"×", B3:B9&","},   "select max(Col3) where not Col2 starts with '×'    group by Col1 pivot Col2"),,7^7)), "×")), ",$", ))

Notice the 7^7 is the (length of the data)^(length of the data). i.e. from 3 to 9, there are 7 data.

userBRuS0
Related