I am trying to create an arrayformula that will look at a "key id" column and gather all text from another column from all rows with the same "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

I am using it with appsheet 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().

Thoughts?

CodePudding user response：

See my comment and questions above. However, if the answer to my parenthetical question there is YES, you can delete everything in Col-C (including the header) and place the following formula in cell C1:

=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))})