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

 

[Gerald Higgins]

How do you know that the last 3 items are WIP ?
Why should it be those 3 items and not some other items ?

It looks like maybe you can do it simply by analysis of the ACCOUNT No., column D.
All items with 116005 sum to zero, and the other three with 116030 do not sum to zero.
Is it as simple as that ?
In which case some formula using SUMIF or SUMIFS may be helpful.

 

[Miratshah]

Hi Gerald,

Thanks for reverting. Yes, in this case the unique identifier is Account number. However in many cases, that is not the case. From 30 transactions for example, 25 will sum to 0 where other 5 wont. Unfortunatey SUMIFs is not very useful in this case.

Is there other formula or VBA we can use? Please note, in many cases, amounts are different. Not just + compensating with -.

Thanks in advance.

 

[Gerald Higgins]

OK, so if we can't use Account Number, how DO we identify the WIP items ?

 

[Miratshah]

OK, so if we can't use Account Number, how DO we identify the WIP items ?

Hi Gerald,

It might sound illogical, but is there a way to identify those transactions sum to 0?

Just to give more perspective, in below case, two different account numbers are used, however all transaction for 1 job sum to 0, hence all are marked as contra in column L.

---

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
90	110	IN9073	1104229	116005	110717373	110126321	25/09/2009	25/09/2009	0.00	1,537.00	-1,537	CONTRA
91	110	IN9073	1104229	116005	110717373	110126321	25/09/2009	25/09/2009	0.00	1,500.00	-1,500	CONTRA
92	110	IN9073	1104229	116005	110759137	110116870	13/01/2009	13/01/2009	1,537.00	0.00	1,537	CONTRA
93	110	IN9073	1104229	116005	110759352	110117534	04/02/2009	04/02/2009	1,500.00	0.00	1,500	CONTRA
769	110	IN9073	1104229	116005	110121160	110200830	10/12/2015	10/12/2015	0.00	1,537.00	-1,537	CONTRA
770	110	IN9073	1104229	116005	110121160	110200830	10/12/2015	10/12/2015	0.00	1,500.00	-1,500	CONTRA
771	110	IN9073	1104229	116030	110731339	110201224	29/12/2015	28/12/2015	3,037.00	0.00	3,037	CONTRA
Sheet1

 

[Gerald Higgins]

Forget about Excel for a moment, you need to know how to do this without Excel.

Give us some sample data showing items that CAN be identified as CONTRA and ALSO some that are NOT CONTRA.

Then explain how we identify them.

Once you have explained a method for doing this outside Excel, then we might be able to come up with a way of replicating that within Excel.

 

[Miratshah]

Hi Gerald,

Okay got the point you are trying to make.

So I manually do following steps:-

1. Select 1 company code from column B.
2. Select 1 Job number from column C.
3. Check if the subtotal in column K (balance in base) is 0. If yes, mark contra against every transaction. A pivot by job number & company code helps achieve this faster.
4. Now its time to check each and every job by company code where subtotal is not 0. I manually filter each job and try to identify if few of the transactions sum to 0 in column K or not. If out of say for example 50 transaction in 1 job, 40 sum to 0, I mark them contra and others as WIP. Sometimes if amounts are not identical, account number filter helps by narrowing the criteria. This does result into mistakes since manually identifying transactions that sum to 0 for so many transactions is a tiresome task.

Let me know if I can help you more on this.

 

[Gerald Higgins]

OK, we can probably replicate steps 1-3 in Excel fairly easily.

But I don't know how to do step 4 in Excel, at least I don't know how to do it consistently and accurately.

 

[Miratshah]

Hi Gerald,

Yes, That is where I am stuck as well. It consumes 2 man days at the moment to do it manually. Can we make use of "Solver" to do this? Just thinking aloud.

 

[Miratshah]

Hi Gerald,

alternatively was wondering, if a code similar to this can be used ?

<code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Function</code>[COLOR=#0A0101][FONT=Consolas] [/FONT][/COLOR]<code class="vb plain" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: black !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">GetCombination(CoinsRange </code><code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code>[COLOR=#0A0101][FONT=Consolas] [/FONT][/COLOR]<code class="vb plain" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: black !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Range, SumCellId </code><code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code>[COLOR=#0A0101][FONT=Consolas] [/FONT][/COLOR]<code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Double</code><code class="vb plain" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: black !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">) </code><code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code>[COLOR=#0A0101][FONT=Consolas] [/FONT][/COLOR]<code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">String</code>[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Dim</code> <code class="vb plain" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: black !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">xStr </code><code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">String</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Dim</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">xSum </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Double</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Dim</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">xCell </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Range</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">xSum = SumCellId</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">For</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Each</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">xCell </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">In</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">CoinsRange</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">        </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">If</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Not</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">(xSum / xCell < 1) </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Then</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">            </code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">xStr = xStr & Int(xSum / xCell) & </code><code class="vb string" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: blue !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">" of "</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">& xCell & </code><code class="vb string" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: blue !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">"  "</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">            </code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">xSum = xSum - (Int(xSum / xCell)) * xCell</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">        </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">If</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Next</code>[/FONT][/COLOR]
[COLOR=#0A0101][FONT=Consolas]<code class="vb spaces" style="color: rgb(221, 0, 85); white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">    </code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">GetCombination = xStr</code>[/FONT][/COLOR]
<code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code>[COLOR=#0A0101][FONT=Consolas] [/FONT][/COLOR]<code class="vb keyword" style="white-space: nowrap; font-size: 1em !important; background: none !important; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Function</code>