Forum Discussion

AndreiMone's avatar
AndreiMone
Copper Contributor
Oct 17, 2023

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...
excel
Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 18, 2023

AndreiMone 

 

Another 365 option

=LET(
  CrtAmount, BYROW(Table1[[Debit]:[Credit]],
    LAMBDA(rw, COUNT(rw))) * Table1[Nr. Crt],
  ConsecCrt, SCAN(MIN(IF(CrtAmount,CrtAmount)),CrtAmount,
    LAMBDA(init,x, IF(x,x,init))
  ),
  Text, REDUCE("Text",FILTER(CrtAmount,CrtAmount),
    LAMBDA(seed,crt, VSTACK(seed,TEXTJOIN(" ",,FILTER(Table1[Text],ConsecCrt=crt))))
  ),
  Amounts, FILTER(Table1[[Debit]:[Credit]], CrtAmount),
  HSTACK(
    VSTACK("Nr. Crt", SEQUENCE(ROWS(Amounts))),
    Text,
    VSTACK({"Debit","Credit"},IF(Amounts,Amounts,""))
  )
)

Resources