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 17, 2023

AndreiMone 

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)
)

 

 

Resources