Excel Formula/VBA-Conditional Sum to find contra from multiple entries

M

Miratshah

Board Regular
Joined
Nov 29, 2016
Messages
57
Hi Experts,

I have a sheet which runs into over 30000 rows. I need to find contra entries within a company in column B for a each job in column C. Basically I need to find which transactions sum to 0 in column K (Balance in Base). so for example, in company code IN9073 in column B, there are 30 transactions which sum to 3,000 in column K (Balance in base). I have manually identified whether in each transaction is contra on not in column L (Marker2). If a particular transaction is not summing to 0, I have marked it as WIP.

The problem is there can be transaction with amount + and - which can be identified AND transactions where different amounts with different signs sum to 0.

I need this to be achieved by way of some formula or VBA. I tried sumifs but failed when there are 10 transactions out of which 8 are contra and 2 are not.

The HTML of my excel is below,

P.s. Rounding off to 1 or 2 is completely allowed :)

Thanks in advance.


Book1
ABCDEFGHIJKL
1Company No.Company CodeJob No.Account No.Trans. No.Journal No.Date PostedEntry DateDebitsCreditsBalance in BaseManual
58110IN9073110695711600511012100111014223830/11/201030/11/20101,000.000.001,000CONTRA
59110IN9073110695711600511012100111014223830/11/201030/11/20102,000.000.002,000CONTRA
60110IN9073110695711600511015472411013261731/03/201029/03/20100.0038,500.00-38,500CONTRA
61110IN9073110695711600511076457711013045308/02/201008/02/20101,000.000.001,000CONTRA
62110IN9073110695711600511076458511013046208/02/201008/02/20101,000.000.001,000CONTRA
63110IN9073110695711600511076461611013049408/02/201008/02/20101,000.000.001,000CONTRA
64110IN9073110695711600511076464011013052408/02/201008/02/20101,000.000.001,000CONTRA
65110IN9073110695711600511076468511013057809/02/201009/02/20101,000.000.001,000CONTRA
66110IN9073110695711600511076472011013062710/02/201010/02/20101,000.000.001,000CONTRA
67110IN9073110695711600511076490511013104322/02/201022/02/20101,000.000.001,000CONTRA
68110IN9073110695711600511076491011013105722/02/201022/02/201012,500.000.0012,500CONTRA
69110IN9073110695711600511076511911013157805/03/201005/03/20101,000.000.001,000CONTRA
70110IN9073110695711600511076523211013179413/03/201013/03/20101,000.000.001,000CONTRA
71110IN9073110695711600511076528811013185013/03/201013/03/20102,000.000.002,000CONTRA
72110IN9073110695711600511076529211013185413/03/201013/03/20103,000.000.003,000CONTRA
73110IN9073110695711600511076529411013185613/03/201013/03/20101,000.000.001,000CONTRA
74110IN9073110695711600511076530911013187113/03/201013/03/20101,000.000.001,000CONTRA
75110IN9073110695711600511076532011013188213/03/201013/03/20101,000.000.001,000CONTRA
76110IN9073110695711600511076534011013190213/03/201013/03/20101,000.000.001,000CONTRA
77110IN9073110695711600511076535511013191715/03/201015/03/20101,000.000.001,000CONTRA
78110IN9073110695711600511076539111013200516/03/201016/03/20101,000.000.001,000CONTRA
79110IN9073110695711600511076554311013229023/03/201023/03/20102,000.000.002,000CONTRA
80110IN9073110695711600511076554411013229123/03/201023/03/20102,000.000.002,000CONTRA
81110IN9073110695711600511076554611013229323/03/201023/03/20102,000.000.002,000CONTRA
82110IN9073110695711600511076640111013445121/05/201021/05/20100.001,000.00-1,000CONTRA
83110IN9073110695711600511076640211013445221/05/201021/05/20100.001,000.00-1,000CONTRA
84110IN9073110695711600511076640311013445421/05/201021/05/20100.001,000.00-1,000CONTRA
85110IN9073110695711603011071968411014101530/10/201028/10/20101,000.000.001,000WIP
86110IN9073110695711603011071968611014101530/10/201028/10/20101,000.000.001,000WIP
87110IN9073110695711603011071968811014101530/10/201028/10/20101,000.000.001,000WIP
Sheet1
 
Last edited:
Sort by date Sort by votes
Akuini said:
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).
Click to expand...

But so many correct entries as per old codes are also marked X now. Infact all are marked X.
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Would it help if I after sorting the data by JOB number, I sort then by amount before running to code?
 
Upvote 0
Miratshah said:
But so many correct entries as per old codes are also marked X now. Infact all are marked X.
Click to expand...

Are you sure?
I change my example (in post 16#) a bit in the second & third section.
The result is the second & third section are marked with X, but the rest is still mark with WPI & blank.


Excel 2013 32 bit
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
1​
QQJob No.QQQQQQQBalance in BaseManual
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​
X
10​
AA
102​
1​
X
11​
AA
102​
10​
X
12​
AA
102​
1​
X
13​
AA
102​
-1​
X
14​
AA
102​
-1​
X
15​
AA
102​
2​
X
9​
16​
AD
302​
3​
X
17​
AD
302​
1​
X
18​
AD
302​
10​
X
19​
AD
302​
1​
X
20​
AD
302​
-5​
X
21​
AD
302​
1​
X
11​
22​
AD
501​
2​
23​
AD
501​
1​
24​
AD
501​
-1​
WPI
25​
AD
501​
1​
26​
AD
501​
-2​
WPI
27​
AD
501​
3​
28​
AD
501​
1​
29​
AD
501​
-1​
WPI
30​
AD
501​
-3​
31​
AD
501​
-5​
-4​
Sheet: Sheet4
 
Upvote 0
Miratshah said:
Would it help if I after sorting the data by JOB number, I sort then by amount before running to code?
Click to expand...

I’m not sure but try something like this:
Using my second code, after running the code, move all section with X mark to another sheet, then sort the data by col K ascending (off course sort by Job No still & always the first criteria), run the code again, repeat the step to move all section with X mark then try again sort the data by col K descending.
 
Upvote 0
Yes, Surprisingly.



Book1
K
3-2,26,800.00
4-2,26,800.00
Sheet5
Cell Formulas
RangeFormula
A1Company No.
B1Company Code
C1Job No.
C28111789
C38111789
C48111789
C58111789
C68111789
C78111789
C88111789
C98111789
C108111789
C118111789
C128111789
C138111789
C148111789
C158111789
C168111789
C178111789
C188111789
C198111789
D1Account No.
E1Trans. No.
F1Journal No.
G1Date Posted
H1Entry Date
I1Debits
J1Credits
K1Balance in Base
K2-291819
K3-226800
K4-226800
K5-226800
K6-226800
K7-226800
K8-226800
K9-226800
K10-226800
K11226800
K12226800
K13226800
K14226800
K15226800
K16226800
K17226800
K18226800
K19291818
L1ManualNewVBA
L2X
L3X
L4X
L5X
L6X
L7X
L8X
L9X
L10X
L11X
L12X
L13X
L14X
L15X
L16X
L17X
L18X
L19X
M1SUMIFNewVBA
M19-1
N1ManualOldVBA
N2CONTRA
N3CONTRA
N4CONTRA
N5CONTRA
N6CONTRA
N7CONTRA
N8CONTRA
N9CONTRA
N10CONTRA
N11CONTRA
N12CONTRA
N13CONTRA
N14CONTRA
N15CONTRA
N16CONTRA
N17CONTRA
N18CONTRA
N19CONTRA
O1SUMIFOldVBA
O19-1
 
Upvote 0
Not sure why that happened.
Then try this, it combine the first & second code.
The X mark will be placed in col N.

Code: 
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1086996c()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1086996-excel-formula-vba-conditional-sum-find-contra-multiple-entries.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] q [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb, vc
[COLOR=Royalblue]Dim[/COLOR] flag [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
n = Range([COLOR=brown]"C"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"C1:C"[/COLOR] & n)
vb = Range([COLOR=brown]"K1:K"[/COLOR] & n)
[COLOR=Royalblue]ReDim[/COLOR] vc([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] n, [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]3[/COLOR])
Range([COLOR=brown]"L1:N"[/COLOR] & n).ClearContents

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    j = i:  x = [COLOR=crimson]0[/COLOR]
    
    [COLOR=Royalblue]Do[/COLOR]
        x = x + vb(i, [COLOR=crimson]1[/COLOR])
        i = i + [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] i > UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = va(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])
        
    i = i - [COLOR=crimson]1[/COLOR]
    
    vc(i, [COLOR=crimson]2[/COLOR]) = x
    
    [COLOR=Royalblue]If[/COLOR] x = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]GoTo[/COLOR] [COLOR=Royalblue]skip[/COLOR]:
    
    [COLOR=Royalblue]If[/COLOR] x < [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]For[/COLOR] k = j [COLOR=Royalblue]To[/COLOR] i
            vb(k, [COLOR=crimson]1[/COLOR]) = vb(k, [COLOR=crimson]1[/COLOR]) * -[COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
        x = x * -[COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
            [COLOR=Royalblue]For[/COLOR] k = j [COLOR=Royalblue]To[/COLOR] i
            
                z = [COLOR=crimson]0[/COLOR]
                
                [COLOR=Royalblue]For[/COLOR] q = j [COLOR=Royalblue]To[/COLOR] k
                z = z + vb(q, [COLOR=crimson]1[/COLOR])
                [COLOR=Royalblue]Next[/COLOR]
                
                flag = [COLOR=Royalblue]False[/COLOR]
                
                    [COLOR=Royalblue]If[/COLOR] z = x [COLOR=Royalblue]Then[/COLOR]
                        vc(k, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]1[/COLOR]: vc(k, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"WPI"[/COLOR]: flag = [COLOR=Royalblue]True[/COLOR]: [COLOR=Royalblue]GoTo[/COLOR] [COLOR=Royalblue]skip[/COLOR]:
                    [COLOR=Royalblue]ElseIf[/COLOR] z > x [COLOR=Royalblue]Then[/COLOR]
                        vb(k, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]
                    [COLOR=Royalblue]Else[/COLOR]
                        [COLOR=Royalblue]If[/COLOR] vb(k, [COLOR=crimson]1[/COLOR]) <= [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                            vb(k, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]
                        [COLOR=Royalblue]Else[/COLOR]
                            vc(k, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"WPI"[/COLOR]
                        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
                    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
                    
            [COLOR=Royalblue]Next[/COLOR]
        
[COLOR=Royalblue]skip[/COLOR]:

        [COLOR=Royalblue]If[/COLOR] flag = [COLOR=Royalblue]False[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            [COLOR=Royalblue]For[/COLOR] k = j [COLOR=Royalblue]To[/COLOR] i
                vc(k, [COLOR=crimson]3[/COLOR]) = [COLOR=brown]"X"[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"L1"[/COLOR]).Resize(UBound(vc, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]3[/COLOR]) = vc
Range([COLOR=brown]"L1"[/COLOR]) = [COLOR=brown]"Manual"[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 
Upvote 0
Not trying to disrespect, is there any logic that we are missing in identifying transactions that sum to sumif of job number? At few places it did work as per 1st code.
 
Upvote 0
Miratshah said:
This gave all blank in column L and all X in column N.
Click to expand...

Could you upload your workbook (without sensitive data) somewhere (maybe via dropbox.com or google drive)?
Then put the link here. Just the data from col B & K would be enough.

Not trying to disrespect, is there any logic that we are missing in identifying transactions that sum to sumif of job number? At few places it did work as per 1st code.
Click to expand...

I don't understand what you mean.
 
Upvote 0
Akuini said:
Could you upload your workbook (without sensitive data) somewhere (maybe via dropbox.com or google drive)?
Then put the link here. Just the data from col B & K would be enough.
Click to expand...

Here you go:-

https://drive.google.com/open?id=1vdyH0Rf_iPH-rMg73NgSH9xbp233r9rN

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.

Let me know in case you need any inputs.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Conditional Quantity sum based on duplicate values in various other columns
Replies
3
Views
323
whassan
W
A
  • Question
To Find the difference of values through their crossing points on the Graph
Replies
2
Views
362
Fluff
Fluff
T
  • Question
SumIfs' formula with Multiple criteria including dates
Replies
3
Views
611
DanteAmor
DanteAmor
N
  • Question
First in First Out (Fifo) Stock balance Calaculation File.
Replies
2
Views
8K
TotallyConfused
T
D
  • Question
Sum Product or Sum IF for criteria in multiple columns
Replies
1
Views
412
cerfani
C

Forum statistics

Threads
1,214,645
Messages
6,120,711
Members
448,984
Latest member
foxpro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top