ArrayFormula with IF statement in Google Sheets (includes a TextJoin)

69 views Asked by At

I'm trying to turn a list of names (Col A) into a list of names (Col B) but where Col B inserts an additional row, duplicates one name + "- Date" (So ultimately I want Column C to be the final output)

Eg.

enter image description here

To get from the Col A to B - I have used

=transpose(split(TEXTJOIN(CHAR(10)&CHAR(10),true,A2:A),CHAR(10),true,false))

I can transfer B2 into C2 with

=if(B2<>"",TextJoin(" - ",false,B2,"Date"),B2)

Can anyone help me turn this into an ArrayFormula for the entire column? Or come up with a better way of doing this?

2

There are 2 answers

0
Mark On BEST ANSWER

What about:

=arrayformula(
    if(B2:B= "", 
       offset(B2:B, -1, 0), 
       concat(B2:B, " - Date")
    )
)
0
rockinfreakshow On

You may try:

=reduce("Output_",tocol(A2:A,1),lambda(a,c,vstack(a,c&" - Date",c)))

enter image description here