Copper Contributor

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.

7 Replies

# Re: Combine consecutive rows only if column is blank. Please advice

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?

# Re: Combine consecutive rows only if column is blank. Please advice

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.

# Re: Combine consecutive rows only if column is blank. Please advice

Power Query option attached...

# Re: Combine consecutive rows only if column is blank. Please advice

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

# Re: Combine consecutive rows only if column is blank. Please advice

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

# Re: Combine consecutive rows only if column is blank. Please advice

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