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

 

[Akuini]

Hey Akuini,

While the code works absolutely brilliant, when I add a big volume, I get an error saying "Error found:Overflow"

Any idea?

Could you be more specific with what you mean by a big volume?


Try replacing all "Integer" with "Long" in this part:

Sub CombinationsNP2(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Integer

so it becomes:

Sub CombinationsNP2(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iElement As Long, iIndex As Long)
Dim i As Long

 

[Miratshah]

Could you be more specific with what you mean by a big volume?

When I tried to run this macro on rows about 60000 rows, It ran once and in next few runs, I got an error of Overflow.

I will try to replace "integer" with "long" as per your suggestion.

 

[Miratshah]

Hi Akuini,

I tried to replace Integer with Long, however still no luck.
Strangely I could execute the old Macro on huge data. Not sure why sometimes it does not run and five overflow error.

One more thing, for 1 job, there can be multiple combinations which are marked as contra. Can this Macro highlight which all combinations are contra with each other? For example, if I have 500 lines of which, 300 are contra, Can i know within these 300, which rows are contra against which rows?