Google Sheets Arrayformula with some sort of concatenate/textjoin/something similar for all rows

Question

I'm trying to do an arrayformula that goes down all rows. I want it to grab the text from a specific row if the row that it's on meets conditions.

Here's an example of what I'm trying to do: have a formula in G5 that iterates through A:F. If the cell = "N", the grab the text from row 4 and concatenate it into a single string, with the text separated by commas.

I've looked at other questions on stack overflow but they're not quite the same.

Here's a sample Sheet I've gotten as far as the formulas in K4 and P4, but can't figure out how to make it all one formula.

Any suggestions on how to do this?

solution in a new tab called MK_Help. I will add to answers if you agree i had the right Idea? — MattKing, Commented Jul 8, 2022 at 18:37
@MattKing thank you! It works perfectly. Unfortunately I can't mark it as a best answer because you added it as a comment, but thank you! — edjusted, Commented Jul 8, 2022 at 22:02

player0 · Accepted Answer · 2022-07-08 19:51:31Z

1

use:

=ARRAYFORMULA(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(A5:F="N", A4:F4&",", )),,9^9))), ",$", ))

answered Jul 8, 2022 at 19:51

player0

1

Add a comment |

Collectives™ on Stack Overflow

Google Sheets Arrayformula with some sort of concatenate/textjoin/something similar for all rows

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related