Sum total when name changes

[Marq1995]

Hi all,

I need you to help me.. Im loosing sleep! haha

What I want to do is sum up the debit and credit column and add the total in the blank row (when the name value changes). But, if the value is <0 I want the total to go in the debit column - if it is >0 I want it to go in the credit column

I hope that makes sense...

Please help!!

I

 

[Peter_SSs]

Could you please explain the code to me??

1. Find the blank cells in column A
2. In columns C:D beside those blank cells put the formulas that I gave in post #5

 

[Marq1995]

1. Find the blank cells in column A
2. In columns C:D beside those blank cells put the formulas that I gave in post #5

What do the below refer to?

R1C:R[-1]C[1],R1C1:R[-1]C1=R[-1]C1)
R1C[-1]:R[-1]C,R1C1:R[-1]C1=R[-1]C1)

 

[Marq1995]

If I moved the debits over to column H and the Credits over to column I, how would the code read?

 

[Peter_SSs]

What do the below refer to?

R1C:R[-1]C[1],R1C1:R[-1]C1=R[-1]C1)
R1C[-1]:R[-1]C,R1C1:R[-1]C1=R[-1]C1)

Some further explanation here but a couple of examples

R1C is a cell reference to row 1 in the current column (that is, the column that the formula is being placed in). So if the formula is in column C it means C$1
R[-1]C is a cell reference to the row immediately above the formula (R[-1]) and the column the formula is in. So if the formula is being placed in D20 this would mean D19
R1C[-1] is a cell reference to row 1 in the column 1 to the left of the formula. So if the formula was being placed in D20 this would mean C1

If I moved the debits over to column H and the Credits over to column I, how would the code read?

All you would nee to change is this one number:

rA.Offset(, 7).Resize(, 2)....

 

[Marq1995]

You are honestly AMAZING!!! This has saved me!

I cannot thank you enough!

 

[Peter_SSs]

You are honestly AMAZING!!! This has saved me!

Thanks for your kind words. Glad to help.

I cannot thank you enough!

You just did.

 

[Marq1995]

I have just noticed that it hasnt calculated the final row
What do I need to adjust?

 

[Peter_SSs]

I have just noticed that it hasnt calculated the final row

OK, the sample in post #3 had "GBP" in what looked like the last row but the sheet must actually continue after that.

Try the modification below.


BTW, the description below does not include the possibility that the sum might be exactly zero. Is that possible and if so which column should the zero go in?

if the value is <0 I want the total to go in the debit column - if it is >0 I want it to go in the credit column

Sub Marq1995()
  Dim rA As Range
  
  For Each rA In Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
    rA.Offset(, 7).Resize(, 2).FormulaR1C1 = Array( _
      "=LET(s,SUM(FILTER(R1C:R[-1]C[1],R1C1:R[-1]C1=R[-1]C1)),IF(s<0,s,""""))", _
      "=LET(s,SUM(FILTER(R1C[-1]:R[-1]C,R1C1:R[-1]C1=R[-1]C1)),IF(s<0,"""",s))")
  Next rA
  Range("A" & Rows.Count).End(xlUp).Offset(1, 7).Resize(, 2).FormulaR1C1 = Array( _
      "=LET(s,SUM(FILTER(R1C:R[-1]C[1],R1C1:R[-1]C1=R[-1]C1)),IF(s<0,s,""""))", _
      "=LET(s,SUM(FILTER(R1C[-1]:R[-1]C,R1C1:R[-1]C1=R[-1]C1)),IF(s<0,"""",s))")
End Sub

 

[Marq1995]

OK, the sample in post #3 had "GBP" in what looked like the last row but the sheet must actually continue after that.

Try the modification below.


BTW, the description below does not include the possibility that the sum might be exactly zero. Is that possible and if so which column should the zero go in?

Sub Marq1995()
  Dim rA As Range
 
  For Each rA In Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
    rA.Offset(, 7).Resize(, 2).FormulaR1C1 = Array( _
      "=LET(s,SUM(FILTER(R1C:R[-1]C[1],R1C1:R[-1]C1=R[-1]C1)),IF(s<0,s,""""))", _
      "=LET(s,SUM(FILTER(R1C[-1]:R[-1]C,R1C1:R[-1]C1=R[-1]C1)),IF(s<0,"""",s))")
  Next rA
  Range("A" & Rows.Count).End(xlUp).Offset(1, 7).Resize(, 2).FormulaR1C1 = Array( _
      "=LET(s,SUM(FILTER(R1C:R[-1]C[1],R1C1:R[-1]C1=R[-1]C1)),IF(s<0,s,""""))", _
      "=LET(s,SUM(FILTER(R1C[-1]:R[-1]C,R1C1:R[-1]C1=R[-1]C1)),IF(s<0,"""",s))")
End Sub

This is brilliant! Thank you so much!

Really good point! It would need to go into the credit column...

 

[Peter_SSs]

This is brilliant! Thank you so much!

You're welcome. Glad to help.

Really good point! It would need to go into the credit column...

That turned out lucky as the existing formulas happen to do that.