Search code examples
regexgoogle-sheetsconcatenationgoogle-sheets-formulagoogle-sheets-query

Concatenate cells between dynamic start and end row with custom formulas

The below code will add the cells in column B between modalOpen and modalClose into a single cell in column C. 

=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)), ">, & ", "> & ")))

examplePage

The below code is used to put cells together with text from the Test Names sheet. 

=IF(B21=testNameButton, CONCATENATE(A21, testNameButtonCodeBlock0, C21, testNameButtonCodeBlock1), 
IF(B21=testNameModal, CONCATENATE(A21, testNameModalCodeBlock0, C21, testNameModalCodeBlock1, REGEXREPLACE(C21,"\s","")),

testsPage

dataPage

The aim is to combine these two formulas together so that I can add the ARRAYFORMULA into the TestNames sheet.

EDIT:
I have added the two but have an issue when there is not a modalOpen directly underneath a modalClose. I also need to remove the modalOpen from the code. 

=IF(B26=testNameButton, CONCATENATE(A26, testNameButtonCodeBlock0, C26, testNameButtonCodeBlock1), 
IF(testNameModal=B26, CONCATENATE(A26, testNameModalCodeBlock0, C26, testNameModalCodeBlock1, ARRAYFORMULA(TRIM(SUBSTITUTE(IFERROR(VLOOKUP(C26, 
SPLIT(TRANSPOSE(SPLIT(QUERY(IF(C26:C<>"", 
IF(B26:B="modalOpen", "♥"&C26:C&"♦"&C26:C, 
IF(B27:B="modalClose", "& <"&C26:C&">", "<"&C26:C&">,")), )
,,999^99), "♥")), "♦"), 2, 0)), ">, & ", "> & ")))),  
IF(B26=testNameTextBox, CONCATENATE(A26, testNameTextBoxCodeBlock0, REGEXREPLACE(C26,"\s",""), testNameTextBoxCodeBlock1, C26, testNameTextBoxCodeBlock2), 
IF(B26=testNameDropDown, CONCATENATE(A26, testNameDropDownCodeBlock0, REGEXREPLACE(C26,"\s",""), testNameDropDownCodeBlock1, C26, testNameDropDownCodeBlock2), 
IF(OR(B26="page", B26="", B26="modalField - textBox", B26="modalField - dropDown", B26="modalClose"), "")))))

enter image description here

Solution

  • here is the fixed first formula in desired format:

    =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, 
 SUBSTITUTE(SPLIT(REGEXEXTRACT(TRANSPOSE(SPLIT(REGEXEXTRACT(QUERY(IF(C2:C<>"", 
 IF(B2:B="modalOpen", "♥"&A2:A&"♦step("""&A2:A&" Fill in "&C2:C&" with", 
 IF(B3:B="modalClose", "& <"&C2:C&">"",♣", "<"&C2:C&">,")), )
 ,,999^99), "(♥.*)"), "♥")), 
 "(.*)♣"), "♦"), ">, & ", "> & " ), 2, 0))&IFERROR(VLOOKUP(A2:A, 
 SUBSTITUTE(SPLIT(REGEXEXTRACT(TRANSPOSE(SPLIT(REGEXEXTRACT(QUERY(IF(C2:C<>"", 
 IF(B2:B="modalOpen", "♥"&A2:A&"♦ async (", 
 IF(B3:B="modalClose", C2:C&") => {await waitFor(1000),♣", C2:C&",")), )
 ,,999^99), "(♥.*)"), "♥")), 
 "(.*)♣"), "♦"), " ( ", " ("), 2, 0)))

    0

    and here is the joint formula:

    =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, 
 SUBSTITUTE(SPLIT(REGEXEXTRACT(TRANSPOSE(SPLIT(REGEXEXTRACT(QUERY(IF(C2:C<>"", 
 IF(B2:B="modalOpen", "♥"&A2:A&"♦step("""&A2:A&" Fill in "&C2:C&" with", 
 IF(B3:B="modalClose", "& <"&C2:C&">"",♣", "<"&C2:C&">,")), )
 ,,999^99), "(♥.*)"), "♥")), 
 "(.*)♣"), "♦"), ">, & ", "> & " ), 2, 0))&IFERROR(VLOOKUP(A2:A, 
 SUBSTITUTE(SPLIT(REGEXEXTRACT(TRANSPOSE(SPLIT(REGEXEXTRACT(QUERY(IF(C2:C<>"", 
 IF(B2:B="modalOpen", "♥"&A2:A&"♦ async (", 
 IF(B3:B="modalClose", C2:C&") => {await waitFor(1000),♣", C2:C&",")), )
 ,,999^99), "(♥.*)"), "♥")), 
 "(.*)♣"), "♦"), " ( ", " ("), 2, 0), 
 IF(B2:B=testNameButton, A2:A&testNameButtonCodeBlock0&C2:C&testNameButtonCodeBlock1, 
 IF(B2:B=testNameModal,  A2:A&testNameModalCodeBlock0&C2:C&testNameModalCodeBlock1&
 REGEXREPLACE(C2:C, "\s", ), ))))

    0