if-statementgoogle-sheets-formulaarray-formulastextjoin

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

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?

Solution

  • What about:

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