I'm trying to concatenate a number of cells into one if they are between two cells with a certain string.
For example: In the Element column there are modalOpen and modalClose and in between those are modalFields. Between modalOpen and modalClose I need to add the Name of each row with Element modalField into the Output column for the modalOpen row.
The number of modalFields can vary from 2 - 20.
delete everything in C column and paste this in C2 cell:
=ARRAYFORMULA(TRIM(SUBSTITUTE(IFERROR(VLOOKUP(B2:B,
SPLIT(TRANSPOSE(SPLIT(QUERY(IF(B2:B<>"",
IF(A2:A="modalOpen", "♥"&B2:B&"♦"&B2:B&" with",
IF(A3:A="modalClose", "& <"&B2:B&">", "<"&B2:B&">,")), )
,,999^99), "♥")), "♦"), 2, 0)), ">, & ", "> & ")))