Search code examples
google-sheets

How to Unpivot table with merged cells?

I'm trying to do a little scheduling automation at my job.

I have a table that I'm not the one filling out. And it has merged cells in dates screenshot

My formula causes empty cells to appear =ArrayFormula(SPLIT(FLATTEN(MAIN!$D$4:$D$51&"|"&MAIN!$E$4:$E$51&"|"&MAIN!$F$3:$M$3&"| "&IF(MAIN!$F$4:$M$51=1,"Работает",MAIN!$F$4:$M$51)),"|",,))

screenshot

i need to get a flat table with correct dates

screenshot

Thanks!

see table https://docs.google.com/spreadsheets/d/1u-gD-f2e0MqoFnB7A8j-FoBiDkfvgSYjPSehuAcR79s/edit?usp=sharing

Solution

  • Within your existing formula → Replace MAIN!$D$4:$D$51 with scan(;MAIN!D4:D51;lambda(a;c;if(c="";a;c))) and you should be good to go

    =ArrayFormula(SPLIT(FLATTEN(scan(;MAIN!D4:D51;lambda(a;c;if(c="";a;c)))&"|"&MAIN!$E$4:$E$51&"|"&MAIN!$F$3:$M$3&"| "&IF(MAIN!$F$4:$M$51=1;"Работает";MAIN!$F$4:$M$51));"|";;))

    enter image description here

    Alternate formula if you wish to try:

    =reduce(tocol(;1);MAIN!F4:M51;lambda(a;c;vstack(a;hstack(index(scan(;MAIN!D1:D51;lambda(f;q;if(q="";f;q)));row(c));index(MAIN!E1:E51;row(c));index(MAIN!3:3;column(c));if(c=1;"Работает";c)))))

    enter image description here