Combine consecutive rows only if column is blank. Please advice

[AndreiMone]

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!

[#2]

@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?

[#3]

@Hans Vogelaar 

[#4]

@AndreiMone 

Here is a macro you can run:

Sub Condense()
    Dim r As Long
    Application.ScreenUpdating = False
    r = 2
    Do
        Do While Range("B" & r + 1).Value <> "" And _
                Range("C" & r + 1).Value = "" And _
                Range("D" & r + 1) = ""
            Range("B" & r).Value = Range("B" & r).Value & _
                " " & Range("B" & r + 1).Value
            Range("B" & r + 1).EntireRow.Delete
        Loop
        r = r + 1
    Loop Until Range("B" & r).Value = ""
    Application.ScreenUpdating = True
End Sub

Others will probably come up with a formula or Power Query solution.

[#5]

Hi @AndreiMone 

Power Query option attached...

[#6]

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

[#7]

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

[#8]

@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,""))
  )
)