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 value 1592.14

B3:B6 combined with C value 100.5

B7:B10 combined with C value 131.55..............and so on.

As I have to work with large data I need to find a formula. 

Any advice? Thanks a lot!

excel

7 Replies

Sort By
  • 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,""))
  )
)
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 17, 2023

    AndreiMone 

    How do you want to combine consecutive rows? Do you want to concatenate the values (i.e. place then behind one another in a single cell)? Or something else?

      • 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