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

[Miratshah]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	Company No.	Company Code	Job No.	Account No.	Trans. No.	Journal No.	Date Posted	Entry Date	Debits	Credits	Balance in Base	Manual
58	110	IN9073	1106957	116005	110121001	110142238	30/11/2010	30/11/2010	1,000.00	0.00	1,000	CONTRA
59	110	IN9073	1106957	116005	110121001	110142238	30/11/2010	30/11/2010	2,000.00	0.00	2,000	CONTRA
60	110	IN9073	1106957	116005	110154724	110132617	31/03/2010	29/03/2010	0.00	38,500.00	-38,500	CONTRA
61	110	IN9073	1106957	116005	110764577	110130453	08/02/2010	08/02/2010	1,000.00	0.00	1,000	CONTRA
62	110	IN9073	1106957	116005	110764585	110130462	08/02/2010	08/02/2010	1,000.00	0.00	1,000	CONTRA
63	110	IN9073	1106957	116005	110764616	110130494	08/02/2010	08/02/2010	1,000.00	0.00	1,000	CONTRA
64	110	IN9073	1106957	116005	110764640	110130524	08/02/2010	08/02/2010	1,000.00	0.00	1,000	CONTRA
65	110	IN9073	1106957	116005	110764685	110130578	09/02/2010	09/02/2010	1,000.00	0.00	1,000	CONTRA
66	110	IN9073	1106957	116005	110764720	110130627	10/02/2010	10/02/2010	1,000.00	0.00	1,000	CONTRA
67	110	IN9073	1106957	116005	110764905	110131043	22/02/2010	22/02/2010	1,000.00	0.00	1,000	CONTRA
68	110	IN9073	1106957	116005	110764910	110131057	22/02/2010	22/02/2010	12,500.00	0.00	12,500	CONTRA
69	110	IN9073	1106957	116005	110765119	110131578	05/03/2010	05/03/2010	1,000.00	0.00	1,000	CONTRA
70	110	IN9073	1106957	116005	110765232	110131794	13/03/2010	13/03/2010	1,000.00	0.00	1,000	CONTRA
71	110	IN9073	1106957	116005	110765288	110131850	13/03/2010	13/03/2010	2,000.00	0.00	2,000	CONTRA
72	110	IN9073	1106957	116005	110765292	110131854	13/03/2010	13/03/2010	3,000.00	0.00	3,000	CONTRA
73	110	IN9073	1106957	116005	110765294	110131856	13/03/2010	13/03/2010	1,000.00	0.00	1,000	CONTRA
74	110	IN9073	1106957	116005	110765309	110131871	13/03/2010	13/03/2010	1,000.00	0.00	1,000	CONTRA
75	110	IN9073	1106957	116005	110765320	110131882	13/03/2010	13/03/2010	1,000.00	0.00	1,000	CONTRA
76	110	IN9073	1106957	116005	110765340	110131902	13/03/2010	13/03/2010	1,000.00	0.00	1,000	CONTRA
77	110	IN9073	1106957	116005	110765355	110131917	15/03/2010	15/03/2010	1,000.00	0.00	1,000	CONTRA
78	110	IN9073	1106957	116005	110765391	110132005	16/03/2010	16/03/2010	1,000.00	0.00	1,000	CONTRA
79	110	IN9073	1106957	116005	110765543	110132290	23/03/2010	23/03/2010	2,000.00	0.00	2,000	CONTRA
80	110	IN9073	1106957	116005	110765544	110132291	23/03/2010	23/03/2010	2,000.00	0.00	2,000	CONTRA
81	110	IN9073	1106957	116005	110765546	110132293	23/03/2010	23/03/2010	2,000.00	0.00	2,000	CONTRA
82	110	IN9073	1106957	116005	110766401	110134451	21/05/2010	21/05/2010	0.00	1,000.00	-1,000	CONTRA
83	110	IN9073	1106957	116005	110766402	110134452	21/05/2010	21/05/2010	0.00	1,000.00	-1,000	CONTRA
84	110	IN9073	1106957	116005	110766403	110134454	21/05/2010	21/05/2010	0.00	1,000.00	-1,000	CONTRA
85	110	IN9073	1106957	116030	110719684	110141015	30/10/2010	28/10/2010	1,000.00	0.00	1,000	WIP
86	110	IN9073	1106957	116030	110719686	110141015	30/10/2010	28/10/2010	1,000.00	0.00	1,000	WIP
87	110	IN9073	1106957	116030	110719688	110141015	30/10/2010	28/10/2010	1,000.00	0.00	1,000	WIP
Sheet1

 

[Miratshah]

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

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

 

[Miratshah]

Would it help if I after sorting the data by JOB number, I sort then by amount before running to code?

 

[Akuini]

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

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
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

1​

[/td][td]Q[/td][td]Q[/td][td]Job No.[/td][td]Q[/td][td]Q[/td][td]Q[/td][td]Q[/td][td]Q[/td][td]Q[/td][td]Q[/td][td]Balance in Base[/td][td]Manual[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

2​

[/td][td][/td][td]AA[/td][td=bgcolor:#BDD7EE]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#BDD7EE]

1​

[/td][td=bgcolor:#FFE699]WPI[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

3​

[/td][td][/td][td]AA[/td][td=bgcolor:#BDD7EE]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#BDD7EE]

1​

[/td][td=bgcolor:#FFE699]WPI[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

4​

[/td][td][/td][td]AA[/td][td=bgcolor:#BDD7EE]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#BDD7EE]

-3​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

5​

[/td][td][/td][td]AA[/td][td=bgcolor:#BDD7EE]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#BDD7EE]

2​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

6​

[/td][td][/td][td]AA[/td][td=bgcolor:#BDD7EE]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#BDD7EE]

-1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

7​

[/td][td][/td][td]AA[/td][td=bgcolor:#BDD7EE]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#BDD7EE]

1​

[/td][td=bgcolor:#FFE699]WPI[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

8​

[/td][td][/td][td]AA[/td][td=bgcolor:#BDD7EE]

101​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#BDD7EE]

2​

[/td][td][/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

9​

[/td][td][/td][td]AA[/td][td=bgcolor:#C6E0B4]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#C6E0B4]

-3​

[/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

10​

[/td][td][/td][td]AA[/td][td=bgcolor:#C6E0B4]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#C6E0B4]

1​

[/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

11​

[/td][td][/td][td]AA[/td][td=bgcolor:#C6E0B4]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#C6E0B4]

10​

[/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

12​

[/td][td][/td][td]AA[/td][td=bgcolor:#C6E0B4]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#C6E0B4]

1​

[/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

13​

[/td][td][/td][td]AA[/td][td=bgcolor:#C6E0B4]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#C6E0B4]

-1​

[/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

14​

[/td][td][/td][td]AA[/td][td=bgcolor:#C6E0B4]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#C6E0B4]

-1​

[/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

15​

[/td][td][/td][td]AA[/td][td=bgcolor:#C6E0B4]

102​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#C6E0B4]

2​

[/td][td]X[/td][td]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

16​

[/td][td][/td][td]AD[/td][td=bgcolor:#F8CBAD]

302​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#F8CBAD]

3​

[/td][td=bgcolor:#FFE699]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

17​

[/td][td][/td][td]AD[/td][td=bgcolor:#F8CBAD]

302​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#F8CBAD]

1​

[/td][td=bgcolor:#FFE699]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

18​

[/td][td][/td][td]AD[/td][td=bgcolor:#F8CBAD]

302​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#F8CBAD]

10​

[/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

19​

[/td][td][/td][td]AD[/td][td=bgcolor:#F8CBAD]

302​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#F8CBAD]

1​

[/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

20​

[/td][td][/td][td]AD[/td][td=bgcolor:#F8CBAD]

302​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#F8CBAD]

-5​

[/td][td]X[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

21​

[/td][td][/td][td]AD[/td][td=bgcolor:#F8CBAD]

302​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#F8CBAD]

1​

[/td][td]X[/td][td]

11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

22​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

2​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

23​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

24​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

-1​

[/td][td=bgcolor:#FFE699]WPI[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

25​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

26​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

-2​

[/td][td=bgcolor:#FFE699]WPI[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

27​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

3​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

28​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

29​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

-1​

[/td][td=bgcolor:#FFE699]WPI[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

30​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

-3​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

31​

[/td][td][/td][td]AD[/td][td=bgcolor:#FFE699]

501​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFE699]

-5​

[/td][td][/td][td]

-4​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

 

[Akuini]

Would it help if I after sorting the data by JOB number, I sort then by amount before running to code?

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.

 

[Miratshah]

Yes, Surprisingly.

Book1
	K
3	-2,26,800.00
4	-2,26,800.00
Sheet5

Cell Formulas
Range	Formula
A1	Company No.
B1	Company Code
C1	Job No.
C2	8111789
C3	8111789
C4	8111789
C5	8111789
C6	8111789
C7	8111789
C8	8111789
C9	8111789
C10	8111789
C11	8111789
C12	8111789
C13	8111789
C14	8111789
C15	8111789
C16	8111789
C17	8111789
C18	8111789
C19	8111789
D1	Account No.
E1	Trans. No.
F1	Journal No.
G1	Date Posted
H1	Entry Date
I1	Debits
J1	Credits
K1	Balance in Base
K2	-291819
K3	-226800
K4	-226800
K5	-226800
K6	-226800
K7	-226800
K8	-226800
K9	-226800
K10	-226800
K11	226800
K12	226800
K13	226800
K14	226800
K15	226800
K16	226800
K17	226800
K18	226800
K19	291818
L1	ManualNewVBA
L2	X
L3	X
L4	X
L5	X
L6	X
L7	X
L8	X
L9	X
L10	X
L11	X
L12	X
L13	X
L14	X
L15	X
L16	X
L17	X
L18	X
L19	X
M1	SUMIFNewVBA
M19	-1
N1	ManualOldVBA
N2	CONTRA
N3	CONTRA
N4	CONTRA
N5	CONTRA
N6	CONTRA
N7	CONTRA
N8	CONTRA
N9	CONTRA
N10	CONTRA
N11	CONTRA
N12	CONTRA
N13	CONTRA
N14	CONTRA
N15	CONTRA
N16	CONTRA
N17	CONTRA
N18	CONTRA
N19	CONTRA
O1	SUMIFOldVBA
O19	-1

 

[Akuini]

Not sure why that happened.
Then try this, it combine the first & second code.
The X mark will be placed in col N.

[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]

 

[Miratshah]

Not sure why that happened.
Then try this, it combine the first & second code.
The X mark will be placed in col N.

This gave all blank in column L and all X in column N.

 

[Miratshah]

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.

 

[Akuini]

This gave all blank in column L and all X in column N.

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.

I don't understand what you mean.

 

[Miratshah]

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.

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.