# Thread: Excel Formula/VBA-Conditional Sum to find contra from multiple entries Thanks:  1 Post #5227673 (1) Likes: 0

1. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Originally Posted by Miratshah
Here you go:-

I have added desired result in column O for reference. This was plotted manually for remaining transaction where total by Job (SumIF) did not return 0.
Ok, I corrected the flaw in my previous code.
Note:
I can see that when the total of each section by Job (SumIF) (and let’s call it “the residual”) is near zero (in your example 1 or 3) then it is considered zero. So what is the limit here? In this code below I'm using 5. It means that if the residual is off ± 5 from zero then it’s considered zero. You may change the limit in this line:
Code:
`LMT = 5 'LMT is the limit (plus & minus) where you considered the sub total of each data section to be the same as zero.`
As I explained before the section with X (in column N) is where the code can't find the solution.
I think I have an idea to improve the code to deal with the X marked part so we can find a better solution, let me now if you’re interested. But for now see if the code below suit you.

Code:
```Sub a1086996d()
'https://www.mrexcel.com/forum/excel-questions/1086996-excel-formula-vba-conditional-sum-find-contra-multiple-entries.html
Dim i As Long, j As Long, n As Long
Dim x As Long, k As Long, z As Long
Dim q As Long, LMT As Long
Dim va, vb, vc
Dim flag As Boolean

Application.ScreenUpdating = False

'Change this to suit
LMT = 5 'LMT is the limit (plus & minus) where you considered the sub total of each data section to be the same as zeru

n = Range("C" & Rows.count).End(xlUp).Row
va = Range("C1:C" & n)
vb = Range("K1:K" & n)
ReDim vc(1 To n, 1 To 3)
Range("L1:N" & n).ClearContents

For i = 2 To UBound(va, 1)
j = i:  x = 0

Do
x = x + vb(i, 1)
i = i + 1
If i > UBound(va, 1) Then Exit Do
Loop While va(i, 1) = va(i - 1, 1)

i = i - 1

vc(i, 2) = x
flag = False

If x >= -LMT And x <= LMT Then flag = True: GoTo skip:

If x < 0 Then
For k = j To i
vb(k, 1) = vb(k, 1) * -1
Next
x = x * -1
End If

For k = j To i

z = 0

For q = j To k
z = z + vb(q, 1)
Next

flag = False

If z = x Then
vc(k, 1) = 1: vc(k, 1) = "WIP": flag = True: GoTo skip:
ElseIf z > x Then
vb(k, 1) = 0
Else
If vb(k, 1) <= 0 Then
vb(k, 1) = 0
Else
vc(k, 1) = "WIP"
End If
End If

Next

skip:

If flag = False Then
For k = j To i
vc(k, 3) = "X"
Next
Else
For k = j To i
If vc(k, 1) <> "WPI" Then vc(k, 1) = "CONTRA"
Next

End If
Next

Range("L1").Resize(UBound(vc, 1), 3) = vc
Range("L1") = "Manual"

Application.ScreenUpdating = True
End Sub```

2. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Please find below few observations on new code:-

1. Logically if here is X in column O, there should not be tag in column L correct? This is since code could not identify whether its Contra or WIP.
2. If there is only 1 transaction in a job, it is still marked as contra. It should become WIP since there is no corresponding transaction to sum to 0.
3. If I total all Contra marked by the code, It is not summing to 0. Contra should sum to 0.
4. There are few transactions marked as WIP however they are actually Contra. Very few though

Originally Posted by Akuini
As I explained before the section with X (in column N) is where the code can't find the solution.
I think I have an idea to improve the code to deal with the X marked part so we can find a better solution, let me now if you’re interested. But for now see if the code below suit you.
I would love to know a better solution.

3. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Originally Posted by Miratshah
Please find below few observations on new code:-
1. Logically if here is X in column O, there should not be tag in column L correct? This is since code could not identify whether its Contra or WIP.
Yes, but actually it doesnt matter, because if there is X it means you must ignore the result in col L because it's wrong. If it's kind of confusing I can put the X in col L, so it will automatically delete all the wrong result in there.

2. If there is only 1 transaction in a job, it is still marked as contra. It should become WIP since there is no corresponding transaction to sum to 0.
Actually that's what the code does, for example row 264. In what row you found it marked as contra?

3. If I total all Contra marked by the code, It is not summing to 0. Contra should sum to 0.
You meant all Contra in whole data (not just a section)? well, it is not summing to 0, since you have problem with rounding number at the first place. For example the residual at row 18 (first section) is -1, but you manually mark all entries as Contra, and that's because you consider -1 as 0, don't you?. That's why I asked you about the limit.

4. There are few transactions marked as WIP however they are actually Contra. Very few though
In what row? I need to check that.

4. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Originally Posted by Akuini
1. Logically if here is X in column O, there should not be tag in column L correct? This is since code could not identify whether its Contra or WIP.
Yes, but actually it doesnt matter, because if there is X it means you must ignore the result in col L because it's wrong. If it's kind of confusing I can put the X in col L, so it will automatically delete all the wrong result in there.
Okay. You can do that.

Originally Posted by Akuini
2. If there is only 1 transaction in a job, it is still marked as contra. It should become WIP since there is no corresponding transaction to sum to 0.
Actually that's what the code does, for example row 264. In what row you found it marked as contra?
Row 264 for Job 8111863 for amount 62,500 is marked as Contra. Similarly Job numbers 8111939, 8111958 and 8111773-13. All have just one line however marked as Contra.

Originally Posted by Akuini
3. If I total all Contra marked by the code, It is not summing to 0. Contra should sum to 0.
You meant all Contra in whole data (not just a section)? well, it is not summing to 0, since you have problem with rounding number at the first place. For example the residual at row 18 (first section) is -1, but you manually mark all entries as Contra, and that's because you consider -1 as 0, don't you?. That's why I asked you about the limit.
I changed the limit to 10 from 5. Post that If I select only Contra marked transactions, I get total of 95,032 due to Jobs mentioned in point number 2.

Originally Posted by Akuini
4. There are few transactions marked as WIP however they are actually Contra. Very few though
In what row? I need to check that.
I think once you execute point 1, WIP will be wiped off with X. So we can ignore this. All WIP for this point are also marked as X.

5. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Originally Posted by Miratshah
Okay. You can do that.

Row 264 for Job 8111863 for amount 62,500 is marked as Contra. Similarly Job numbers 8111939, 8111958 and 8111773-13. All have just one line however marked as Contra.

I changed the limit to 10 from 5. Post that If I select only Contra marked transactions, I get total of 95,032 due to Jobs mentioned in point number 2.

I think once you execute point 1, WIP will be wiped off with X. So we can ignore this. All WIP for this point are also marked as X.
Ok, try this:
Code:
```Sub a1086996e()
'https://www.mrexcel.com/forum/excel-questions/1086996-excel-formula-vba-conditional-sum-find-contra-multiple-entries.html
Dim i As Long, j As Long, n As Long
Dim x As Long, k As Long, z As Long
Dim q As Long, LMT As Long
Dim va, vb, vc
Dim flag As Boolean, oneline As Boolean

Application.ScreenUpdating = False

'Change this to suit
LMT = 10 'LMT is the limit (plus & minus) where you considered the sub total of each data section to be the same as zero

n = Range("C" & Rows.count).End(xlUp).Row
va = Range("C1:C" & n)
vb = Range("K1:K" & n)
ReDim vc(1 To n, 1 To 3)
Range("L1:N" & n).ClearContents

For i = 2 To UBound(va, 1)
j = i:  x = 0

Do
x = x + vb(i, 1)
i = i + 1
If i > UBound(va, 1) Then Exit Do
Loop While va(i, 1) = va(i - 1, 1)

i = i - 1

vc(i, 2) = x

flag = False
If x >= -LMT And x <= LMT Then flag = True: GoTo skip:

oneline = False
If j = i Then oneline = True: flag = True: GoTo skip:

If x < 0 Then
For k = j To i
vb(k, 1) = vb(k, 1) * -1
Next
x = x * -1
End If

For k = j To i

z = 0

For q = j To k
z = z + vb(q, 1)
Next

flag = False

If z = x Then
vc(k, 1) = 1: vc(k, 1) = "WIP": flag = True: GoTo skip:
ElseIf z > x Then
vb(k, 1) = 0
Else
If vb(k, 1) <= 0 Then
vb(k, 1) = 0
Else
vc(k, 1) = "WIP"
End If
End If

Next

skip:

If flag = False Then
For k = j To i
vc(k, 1) = "X"
Next
Else
For k = j To i
If vc(k, 1) <> "WIP" Then vc(k, 1) = "CONTRA": flag = False
If oneline = True Then oneline = False: vc(k, 1) = "WIP"
Next

End If
Next

Range("L1").Resize(UBound(vc, 1), 3) = vc
Range("L1") = "By Macro"

Application.ScreenUpdating = True
End Sub```

6. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Okay this looks much better. This code identifies all contra where SUMIF for those jobs would result in either 0 or under 10. Also identifies WIP where there is just one transaction for a job.

Is there a way to mark following transactions Contra since they are same amount with different signs within same job number?

For example:-
Job number - 8111876
Row number - 265, 266, 267, 268, 269, 271, 272, 274, 275, 276, 277, 278

If not, can we identify Row number 270, 273 and 278 as WIP because their is the total of Job number as mentioned in column M?

As of now, All transactions in this job are marked as X and this is the case where Job total is beyond our rounding limit of 10.

7. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Originally Posted by Miratshah
Okay this looks much better. This code identifies all contra where SUMIF for those jobs would result in either 0 or under 10. Also identifies WIP where there is just one transaction for a job.

Is there a way to mark following transactions Contra since they are same amount with different signs within same job number?

For example:-
Job number - 8111876
Row number - 265, 266, 267, 268, 269, 271, 272, 274, 275, 276, 277, 278

If not, can we identify Row number 270, 273 and 278 as WIP because their is the total of Job number as mentioned in column M?

As of now, All transactions in this job are marked as X and this is the case where Job total is beyond our rounding limit of 10.
I"ll see what I can do, I'll be back tomorrow when I have time.

8. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Originally Posted by Akuini
I"ll see what I can do, I'll be back tomorrow when I have time.
Sure. I will be waiting. I am gaining some confidence that you will be able to crack the logic.

9. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Originally Posted by Miratshah
Sure. I will be waiting. I am gaining some confidence that you will be able to crack the logic.
I modified my code to get better result. I also use & modified a code that originally is written by John Coleman:
https://stackoverflow.com/questions/...-a-list-in-vba

Note:

The code is in “Module1”.
Run “Sub findWIP”, it will run “Sub removeMatch()” & “Function ListSubsets1”.
Run “Sub checksum”. It will generate the sub total of WIP in each section in column N & format the cell with WIP with brown.
If you want each section have alternate color then run “Sub toColor()”.

The workbook:
https://www.dropbox.com/s/uoke6why38...ries.xlsm?dl=0

10. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Originally Posted by Akuini
I modified my code to get better result. I also use & modified a code that originally is written by John Coleman:
https://stackoverflow.com/questions/...-a-list-in-vba
This is very humble of you give credit.

Originally Posted by Akuini
Note:

The code is in “Module1”.
Run “Sub findWIP”, it will run “Sub removeMatch()” & “Function ListSubsets1”.
Run “Sub checksum”. It will generate the sub total of WIP in each section in column N & format the cell with WIP with brown.
If you want each section have alternate color then run “Sub toColor()”.

The workbook:
https://www.dropbox.com/s/uoke6why38...ries.xlsm?dl=0
I checked it for a smaller data size with just 1 job number and it ran perfectly fine. The only concern is, it takes huge amount of time to run over even 100 rows. I am wondering will I be able to run this over 30000 rows.