Forum Discussion
AndreiMone
Oct 17, 2023Copper Contributor
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...
AndreiMone
Oct 17, 2023Copper Contributor
Lorenzo
Oct 17, 2023Silver Contributor
A 365 option (attached)
=LET(
filled, IF(Table1[Debit]&Table1[Credit]="","",Table1[Nr. Crt]),
group, SCAN(MIN(filled), filled,
LAMBDA(init,x, IF(x="", init, x))
),
Total, LAMBDA(array,col,
LET(
t, SUM(CHOOSECOLS(array,col)),
IF(t, t, "")
)
),
Combine, LAMBDA(seed,grp,
LET(
flt, FILTER(Table1[[Text]:[Credit]], group=grp),
VSTACK(seed, HSTACK(TEXTJOIN(" ",,CHOOSECOLS(flt,1)), Total(flt,2), Total(flt,3)))
)
),
data, REDUCE({"Text","Debit","Credit"}, UNIQUE(group), Combine),
HSTACK(VSTACK("Nr. Crt", SEQUENCE(ROWS(data)-1)), data)
)