Solved: Gooogle Sheets - Arrayformula - Gather all text fr... - Page 2

Tom_Stevens · 12-15-2021 06:55 AM

This is and indirect app sheet question, regarding google sheets.

I am trying to create an arrayformula in sheets that will look at a related “key id” column and gather all text from another column from all rows with the same related “key id”:

– col A / col B / col C

1- KEY ID / NOTE / COMPILED NOTES

2- A1BZ / TACO / TACO FROG

3- T212 / CORN / CORN APPLE

4- T212 / APPLE / CORN APPLE

5- CD41 / DOG / DOG

6- A1BZ / FROG / TACO FROG

(Ignore the fact that rows cannot share a KEY ID, I know this. It’s just simplified to display the idea).

I am using it as a back end formula and as the sheet is active (having rows removed and added I need this to be an arrayformula. I thought some type of JOIN() with an IF statement. But I’ve note come up with anything that works. I can make it work if it’s not an array formula with a FILTER(), but the formula doesn’t always auto propagate into new rows.

I understand that there are ways to do this directly in Appsheet, but the issue is in keeping the data “live” with out having to “update” each affected row. Again I understand I could create an action to do this, but the back end sheet solution seemed the simplest.

Any thoughts from the hive mind?

Tom_Stevens

On a related note the “arrayformula” for the google sheets side would look like this for my demo example above.

=ArrayFormula({“COMPILED NOTES”; IF(A2:A="",VLOOKUP(A2:A,TRIM(SPLIT(FLATTEN(QUERY(QUERY({A2:A&"~",B2:B},“Select MAX(Col2) WHERE Col2 Is Not Null GROUP BY Col2 PIVOT Col1”),9^9)),"~")),2,FALSE))})

And the fully functional table side of google sheets the final product looks like this:

=ArrayFormula({“COMPILED NOTES”; IF(I2:I="", VLOOKUP(I2:I,TRIM(SPLIT(FLATTEN(QUERY(QUERY({I2:I&"~",N2:N},“Select MAX(Col2) WHERE Col2 Is Not Null GROUP BY Col2 PIVOT Col1”),9^9)),"~")),2,FALSE))})

View solution in original post

Gooogle Sheets - Arrayformula - Gather all text from rows with same key id