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

       

       

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Oct 17, 2023

      AndreiMone 

      =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.

Resources