I’m working on a live Google Sheet with 25+ other users and some cells are merged. I want to create a helper sheet that fills down the info from merged cells while preserving intentional blank rows.
I’ve attempted using VLOOKUP with FILTER to fill down the values, but I can’t get it to correctly handle both the merged cells and preserve the intentional blank rows.
Please see sample Google Sheet which includes an array formula attempt.
I want to avoid using Apps Script since I had issues with it when I ran some other script hourly.
Current arrayformula (not working properly): =ARRAYFORMULA(IF(ROW(main!A:A)=1,main!A:A,IF(NOT(ISBLANK(main!A:A)),main!A:A,"")))
Here’s a generalized approach which you may adapt accordingly:
=let(Λ,max(bycol(A:G,lambda(Σ,index(match(,0/(Σ<>"")))))),
Ξ,lambda(Σ,scan(,indirect(Σ&Λ),lambda(a,c,if(cell("prefix",c)<>"",c,a)))),
hstack(Ξ("A2:A"),Ξ("B2:B"),indirect("C2:E"&Λ),Ξ("F2:F"),indirect("G2:G"&Λ)))