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

I don't know.
I'm not a code expert and I have not used Solver very much.

There are threads on this board which address similar problems, about having some number of transactions and having to net some of them off against each other.
Try searching for them.  Reply With Quote

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

Hi, Miratshah
Question:
1. Your data in column K, are they double type/decimal or just integer?
If they are decimal then I think it's hard to find a solution.

P.s. Rounding off to 1 or 2 is completely allowed
That's what bothers me.

2. If they are integer, how about simplifying the logic, like this:
Using your example in post #1 , we can easily find that for that particular company & job the subtotal in col K is 3000. Using vba, why not just looping in col K until we find some entry which subtotal is 3000, then mark those entries as "WIP" and the rest will be "CONTRA". If we loop from the top then we will mark row 58-59 as WIP or if we loop from the bottom we will mark row 85-87 as WIP.  Reply With Quote

3. Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries Originally Posted by Akuini Hi, Miratshah
Question:
1. Your data in column K, are they double type/decimal or just integer?
If they are decimal then I think it's hard to find a solution.

That's what bothers me.

2. If they are integer, how about simplifying the logic, like this:
Using your example in post #1 , we can easily find that for that particular company & job the subtotal in col K is 3000. Using vba, why not just looping in col K until we find some entry which subtotal is 3000, then mark those entries as "WIP" and the rest will be "CONTRA". If we loop from the top then we will mark row 58-59 as WIP or if we loop from the bottom we will mark row 85-87 as WIP.
Hi Akuini,

What I can do is, before starting my work, I can convert all decimal to integer by using round off formula. This is not an issue.

I believe point 2 in your post is a wonderful idea. However, can the VBA you are suggesting work in loop for each company code by Job? Please note that amounts will not always be straight forward + or -.

I am now little optimistic that solution is not too far.

Thanks.  Reply With Quote

4. Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries Originally Posted by Miratshah Hi Akuini,
I believe point 2 in your post is a wonderful idea. However, can the VBA you are suggesting work in loop for each company code by Job? Please note that amounts will not always be straight forward + or -.
Thanks.
The Job No is unique for each company, right? I mean 1 company can have 2 or more Job No but 2 companies can’t have the same Job No, correct? For example: job no 1104229 is only for company IN9073
So is it correct to assume that the data grouping for each analysis can be based on Job No only?  Reply With Quote

5. Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries Originally Posted by Akuini The Job No is unique for each company, right? I mean 1 company can have 2 or more Job No but 2 companies can’t have the same Job No, correct? For example: job no 1104229 is only for company IN9073
So is it correct to assume that the data grouping for each analysis can be based on Job No only?
Yes, It is safe to assume that the data grouping for each analysis can be based on Job number only.  Reply With Quote

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

Ok, try this:
I use a mock-up data to test the code.
The code only marks the WPI, the blank means they are CONTRA.
In col M the code add the sub total of each data group.

Code:
Sub a1086996b()
'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
Dim va, vb, vc

Application.ScreenUpdating = False
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 2)
Range("L1:M" & 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

If x = 0 Then 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

Debug.Print z

If z = x Then
vc(k, 1) = 1: vc(k, 1) = "WPI": 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) = "WPI"
End If
End If

Next

skip:

Next

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

Application.ScreenUpdating = True
End Sub

RESULT:

Excel 2013 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
1
Company No. Company Code Job No. Account No. Trans. No. Journal No. Date Posted Entry Date Debits Credits Balance in Base Manual
2
AA
101
1
WPI
3
AA
101
1
WPI
4
AA
101
-3
5
AA
101
2
6
AA
101
-1
7
AA
101
1
WPI
8
AA
101
2
3
9
AA
102
-3
10
AA
102
1
11
AA
102
-1
12
AA
102
1
13
AA
102
-1
14
AA
102
1
15
AA
102
2
0
16
302
3
WPI
17
302
1
WPI
18
302
-1
19
302
1
20
302
-1
21
302
1
4
22
501
2
23
501
1
24
501
-1
WPI
25
501
1
26
501
-2
WPI
27
501
3
28
501
1
29
501
-1
WPI
30
501
-3
31
501
-5
-4
 Sheet: Sheet1  Reply With Quote

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

So I tried this code in a bigger sample. I am happy to report that it works for the most part however there are places where I found incorrect results. I sorted Job numbers in ascending order so that all job numbers are one after the other, this reduced number of errors for sure. However still there are few places with incorrect results. Column N is where manual contra & WIP are ploted against each transactions and column O is where I am comparing the result if VBA to manual. Sorry for the delay, unfortunately complete HTML was not getting uploaded hence reduced few rows.

ABCDEFGHIJKLMNO
1Company No.Company codeJob No.Account No.Trans. No.Journal No.Date PostedEntry DateDebitsCreditsBalance in BaseManualSumIFByJobManualPlottingCheck
28111821787.00WIPCONTRAFALSE
381118211,021.00WIPCONTRAFALSE
481118211,176.00WIPCONTRAFALSE
581118211,078.00WIPCONTRAFALSE
681118219,69,650.00CONTRAWIPFALSE
78111821-4,062.00CONTRA9,69,650.00CONTRATRUE
8811187618,136.00WIPWIPTRUE
981118761,50,000.00CONTRAWIPFALSE
1081118762,07,761.00CONTRAWIPFALSE
118111876-18,136.00CONTRACONTRATRUE
12811187618,136.00WIPCONTRAFALSE
138111876-1,25,181.00CONTRACONTRATRUE
148111876-1,50,000.00CONTRACONTRATRUE
1581118761,25,181.00CONTRACONTRATRUE
1681118761,50,000.00CONTRACONTRATRUE
178111876-1,25,181.00CONTRACONTRATRUE
188111876-1,50,000.00CONTRACONTRATRUE
1981118761,25,181.00CONTRACONTRATRUE
2081118761,50,000.00CONTRACONTRATRUE
218111876-1,25,181.00CONTRAWIPFALSE
228111876-1,50,000.00CONTRA1,00,716.00WIPFALSE
23811191026,430.00WIPWIPTRUE
2481119105,915.00WIPWIPTRUE
2581119106,05,234.00CONTRAWIPFALSE
2681119103,08,648.00WIPWIPTRUE
2781119101,75,824.00CONTRAWIPFALSE
28811191043,952.00CONTRAWIPFALSE
298111910-32,346.00CONTRACONTRATRUE
30811191032,346.00CONTRACONTRATRUE
318111910-6,37,580.00CONTRACONTRATRUE
3281119106,37,580.00CONTRACONTRATRUE
338111910-11,66,005.00CONTRACONTRATRUE
3481119105,50,000.00CONTRAWIPFALSE
35811191011,66,005.00CONTRACONTRATRUE
368111910-9,97,710.00CONTRAWIPFALSE
378111910-3,50,000.00CONTRA3,68,293.00WIPFALSE

Sheet1

Worksheet Formulas
CellFormula
O2=N2=L2
O3=N3=L3
O4=N4=L4
O5=N5=L5
O6=N6=L6
O7=N7=L7
O8=N8=L8
O9=N9=L9
O10=N10=L10
O11=N11=L11
O12=N12=L12
O13=N13=L13
O14=N14=L14
O15=N15=L15
O16=N16=L16
O17=N17=L17
O18=N18=L18
O19=N19=L19
O20=N20=L20
O21=N21=L21
O22=N22=L22
O23=N23=L23
O24=N24=L24
O25=N25=L25
O26=N26=L26
O27=N27=L27
O28=N28=L28
O29=N29=L29
O30=N30=L30
O31=N31=L31
O32=N32=L32
O33=N33=L33
O34=N34=L34
O35=N35=L35
O36=N36=L36
O37=N37=L37  Reply With Quote

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

Sorry, after reading your last data I don’t think I can find a good solution for your problem. So maybe someone else here could help.
But I added some lines in the code just to mark the section (with X) where the code can't find the solution, so at least you know where to look, to do it manually.
Here’s the revised code:

Code:
Sub a1086996c()
'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
Dim va, vb, vc
Dim flag As Boolean

Application.ScreenUpdating = False
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 2)
Range("L1:M" & 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

If x = 0 Then 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) = "WPI": 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) = "WPI"
End If
End If

Next

skip:

If flag = False Then
For k = j To i
vc(k, 1) = "X"
Next
End If
Next

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

Application.ScreenUpdating = True
End Sub  Reply With Quote

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

Yes, even that will save lot of time. However the code you just sent marked every transaction as X. Did something change in the code?  Reply With Quote

10. Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries Originally Posted by Miratshah Yes, even that will save lot of time. However the code you just sent marked every transaction as X. Did something change in the code?

Yes, as I said its to mark the section (with X) where the code can't find the solution. For the section that the code can find the solution it will mark with WPI ( and blank).  Reply With Quote

User Tag List

Tags for this Thread

000, contra, excel & formula, excel & vba, sumifs  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•