Search code examples
google-sheetsgoogle-sheets-formula

Concatenate merged cells with non sequential columns

I have a table where I am trying to concatenate merged cell with other column values

enter image description here I used this formula to get the concatenation done,

=ArrayFormula(query(byrow(
{lookup(row(A3:A),row(A3:A)/(A3:A<>""),A3:A),D3:G},
lambda(r,textjoin("-",1,r))),
"limit "&-1+max(F3:G11<>"",row(F3:G11))))

but I want to exclude column by name BF from the concatenation.

I only want Name, Frequency, Old Measure and New Measure columns to be concatenated

Please help in guiding me here. @ztiaa @Martin

Solution

  • I think you just need to split up your array ranges in your byRow array with a comma. Try this...

    =ArrayFormula(query(byrow(
{lookup(row(A3:A),row(A3:A)/(A3:A<>""),A3:A),D3:D,F3:G},
lambda(r,textjoin("-",1,r))),
"select Col1 limit "&-1+max(F3:G11<>"",row(F3:G11))))