Forum Discussion
Combine consecutive rows only if column is blank. Please advice
Hello, Could use some advice. I have to combine rows from B column if C or D column is blank, ultill the next C cell with value. In this case above, B2 sould remain only values from B2, with D 2 va...
=LET(rng,C1:D10,
rows,BYROW(rng,LAMBDA(x,IF(SUM(LEN(x))>0,ROW(x),""))),
filldown,SCAN("",rows,LAMBDA(x,y,IF(ISNUMBER(y),y,x))),
combine,BYROW(UNIQUE(filldown),LAMBDA(x,TEXTJOIN(" ",,FILTER(B1:B10,filldown=x)))),
skiprows,IF(FILTER(rng,BYROW(rng,LAMBDA(x,SUM(LEN(x)))))=0,"",FILTER(rng,BYROW(rng,LAMBDA(x,SUM(LEN(x)))))),
count,SCAN(0,UNIQUE(filldown),LAMBDA(x,y,ISNUMBER(y)+x)),
HSTACK(count,combine,skiprows))
This returns the desired output in my Excel for the web sheet. The ranges B1:B10 and C1:D10 can be changed according to the actual size of the dataset.