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

Miratshah · Feb 11, 2019

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

1,000.00

0.00

1,000

CONTRA

59

110

IN9073

1106957

116005

110121001

110142238

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

1,000.00

0.00

1,000

CONTRA

62

110

IN9073

1106957

116005

110764585

110130462

08/02/2010

1,000.00

0.00

1,000

CONTRA

63

110

IN9073

1106957

116005

110764616

110130494

08/02/2010

1,000.00

0.00

1,000

CONTRA

64

110

IN9073

1106957

116005

110764640

110130524

08/02/2010

1,000.00

0.00

1,000

CONTRA

65

110

IN9073

1106957

116005

110764685

110130578

09/02/2010

1,000.00

0.00

1,000

CONTRA

66

110

IN9073

1106957

116005

110764720

110130627

10/02/2010

1,000.00

0.00

1,000

CONTRA

67

110

IN9073

1106957

116005

110764905

110131043

22/02/2010

1,000.00

0.00

1,000

CONTRA

68

110

IN9073

1106957

116005

110764910

110131057

22/02/2010

12,500.00

0.00

12,500

CONTRA

69

110

IN9073

1106957

116005

110765119

110131578

05/03/2010

1,000.00

0.00

1,000

CONTRA

70

110

IN9073

1106957

116005

110765232

110131794

13/03/2010

1,000.00

0.00

1,000

CONTRA

71

110

IN9073

1106957

116005

110765288

110131850

13/03/2010

2,000.00

0.00

2,000

CONTRA

72

110

IN9073

1106957

116005

110765292

110131854

13/03/2010

3,000.00

0.00

3,000

CONTRA

73

110

IN9073

1106957

116005

110765294

110131856

13/03/2010

1,000.00

0.00

1,000

CONTRA

74

110

IN9073

1106957

116005

110765309

110131871

13/03/2010

1,000.00

0.00

1,000

CONTRA

75

110

IN9073

1106957

116005

110765320

110131882

13/03/2010

1,000.00

0.00

1,000

CONTRA

76

110

IN9073

1106957

116005

110765340

110131902

13/03/2010

1,000.00

0.00

1,000

CONTRA

77

110

IN9073

1106957

116005

110765355

110131917

15/03/2010

1,000.00

0.00

1,000

CONTRA

78

110

IN9073

1106957

116005

110765391

110132005

16/03/2010

1,000.00

0.00

1,000

CONTRA

79

110

IN9073

1106957

116005

110765543

110132290

23/03/2010

2,000.00

0.00

2,000

CONTRA

80

110

IN9073

1106957

116005

110765544

110132291

23/03/2010

2,000.00

0.00

2,000

CONTRA

81

110

IN9073

1106957

116005

110765546

110132293

23/03/2010

2,000.00

0.00

2,000

CONTRA

82

110

IN9073

1106957

116005

110766401

110134451

21/05/2010

0.00

1,000.00

-1,000

CONTRA

83

110

IN9073

1106957

116005

110766402

110134452

21/05/2010

0.00

1,000.00

-1,000

CONTRA

84

110

IN9073

1106957

116005

110766403

110134454

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

Gerald Higgins · Feb 12, 2019

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.

Akuini · Feb 12, 2019

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.

Miratshah · Feb 13, 2019

Akuini said:
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.

Akuini · Feb 13, 2019

Miratshah said:
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?

Miratshah · Feb 13, 2019

Akuini said:
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.

Akuini · Feb 13, 2019

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:

[FONT=lucida console][color=Royalblue]Sub[/color] a1086996b()
[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

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]2[/color])
Range([color=brown]"L1:M"[/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]
                
                Debug.Print z
                    
                    [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]: [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]Next[/color]

Range([color=brown]"L1"[/color]).Resize(UBound(vc, [color=crimson]1[/color]), [color=crimson]2[/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]

RESULT:

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]Company No.[/td][td]Company Code[/td][td]Job No.[/td][td]Account No.[/td][td]Trans. No.[/td][td]Journal No.[/td][td]Date Posted[/td][td]Entry Date[/td][td]Debits[/td][td]Credits[/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][/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][/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]

-1

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

0

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

-1

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

-1

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

4

[/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: Sheet1[/td][/tr][/table]

Miratshah · Feb 13, 2019

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.

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Company No.

Company code

Job No.

Account No.

Trans. No.

Journal No.

Date Posted

Entry Date

Debits

Credits

Balance in Base

Manual

SumIFByJob

ManualPlotting

Check

2

8111821

787.00

WIP

CONTRA

FALSE

3

8111821

1,021.00

WIP

CONTRA

FALSE

4

8111821

1,176.00

WIP

CONTRA

FALSE

5

8111821

1,078.00

WIP

CONTRA

FALSE

6

8111821

9,69,650.00

CONTRA

WIP

FALSE

7

8111821

-4,062.00

CONTRA

9,69,650.00

CONTRA

TRUE

8

8111876

18,136.00

WIP

TRUE

9

8111876

1,50,000.00

CONTRA

WIP

FALSE

10

8111876

2,07,761.00

CONTRA

WIP

FALSE

11

8111876

-18,136.00

CONTRA

TRUE

12

8111876

18,136.00

WIP

CONTRA

FALSE

13

8111876

-1,25,181.00

CONTRA

TRUE

14

8111876

-1,50,000.00

CONTRA

TRUE

15

8111876

1,25,181.00

CONTRA

TRUE

16

8111876

1,50,000.00

CONTRA

TRUE

17

8111876

-1,25,181.00

CONTRA

TRUE

18

8111876

-1,50,000.00

CONTRA

TRUE

19

8111876

1,25,181.00

CONTRA

TRUE

20

8111876

1,50,000.00

CONTRA

TRUE

21

8111876

-1,25,181.00

CONTRA

WIP

FALSE

22

8111876

-1,50,000.00

CONTRA

1,00,716.00

WIP

FALSE

23

8111910

26,430.00

WIP

TRUE

24

8111910

5,915.00

WIP

TRUE

25

8111910

6,05,234.00

CONTRA

WIP

FALSE

26

8111910

3,08,648.00

WIP

TRUE

27

8111910

1,75,824.00

CONTRA

WIP

FALSE

28

8111910

43,952.00

CONTRA

WIP

FALSE

29

8111910

-32,346.00

CONTRA

TRUE

30

8111910

32,346.00

CONTRA

TRUE

31

8111910

-6,37,580.00

CONTRA

TRUE

32

8111910

6,37,580.00

CONTRA

TRUE

33

8111910

-11,66,005.00

CONTRA

TRUE

34

8111910

5,50,000.00

CONTRA

WIP

FALSE

35

8111910

11,66,005.00

CONTRA

TRUE

36

8111910

-9,97,710.00

CONTRA

WIP

FALSE

37

8111910

-3,50,000.00

CONTRA

3,68,293.00

WIP

FALSE

Sheet1

Cell Formulas
Range	Formula
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

Akuini · Feb 13, 2019

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:

[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]2[/COLOR])
Range([COLOR=brown]"L1:M"[/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]1[/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]2[/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 · Feb 13, 2019

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?

Akuini · Feb 13, 2019

Miratshah said:
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).

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

Miratshah

Board Regular

Gerald Higgins

Well-known Member

Excel Facts

Akuini

Well-known Member

Miratshah

Board Regular

Akuini

Well-known Member

Miratshah

Board Regular

Akuini

Well-known Member

Miratshah

Board Regular

Akuini

Well-known Member

Miratshah

Board Regular

Akuini

Well-known Member

Similar threads

Forum statistics

Share this page

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

Board Regular

Well-known Member

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock