使用自定义公式在动态开始行和结束行之间连接单元格

debugcn 发表于 Dev

特里斯坦C

下面的代码会将B列中位于modalOpen和modalClose之间的单元格添加到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)), ">, & ", "> & ")))

以下代码用于将单元格和“测试名称”表中的文本放在一起。

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

目的是将这两个公式结合在一起，以便我可以将它们添加ARRAYFORMULA到TestNames表中。

编辑：
我已经添加了两个，但有一个问题，当没有modalOpen直接在modalClose下面。我还需要从代码中删除modalOpen。

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

玩家0

这是所需格式的固定第一个公式：

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

这是联合公式：

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