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

Miratshah

Board Regular
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.

[HR][/HR]
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Company No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Company Code</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Job No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Account No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Trans. No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Journal No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Date Posted</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Entry Date</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Debits</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Credits</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;"> Balance in Base </td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Manual</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">58</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110121001</td><td style="text-align: right;;">110142238</td><td style="text-align: right;;">30/11/2010</td><td style="text-align: right;;">30/11/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">59</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110121001</td><td style="text-align: right;;">110142238</td><td style="text-align: right;;">30/11/2010</td><td style="text-align: right;;">30/11/2010</td><td style="text-align: right;;">2,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 2,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">60</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110154724</td><td style="text-align: right;;">110132617</td><td style="text-align: right;;">31/03/2010</td><td style="text-align: right;;">29/03/2010</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">38,500.00</td><td style="text-align: right;;"> -38,500 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">61</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110764577</td><td style="text-align: right;;">110130453</td><td style="text-align: right;;">08/02/2010</td><td style="text-align: right;;">08/02/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">62</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110764585</td><td style="text-align: right;;">110130462</td><td style="text-align: right;;">08/02/2010</td><td style="text-align: right;;">08/02/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">63</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110764616</td><td style="text-align: right;;">110130494</td><td style="text-align: right;;">08/02/2010</td><td style="text-align: right;;">08/02/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">64</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110764640</td><td style="text-align: right;;">110130524</td><td style="text-align: right;;">08/02/2010</td><td style="text-align: right;;">08/02/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">65</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110764685</td><td style="text-align: right;;">110130578</td><td style="text-align: right;;">09/02/2010</td><td style="text-align: right;;">09/02/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">66</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110764720</td><td style="text-align: right;;">110130627</td><td style="text-align: right;;">10/02/2010</td><td style="text-align: right;;">10/02/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">67</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110764905</td><td style="text-align: right;;">110131043</td><td style="text-align: right;;">22/02/2010</td><td style="text-align: right;;">22/02/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">68</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110764910</td><td style="text-align: right;;">110131057</td><td style="text-align: right;;">22/02/2010</td><td style="text-align: right;;">22/02/2010</td><td style="text-align: right;;">12,500.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 12,500 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">69</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765119</td><td style="text-align: right;;">110131578</td><td style="text-align: right;;">05/03/2010</td><td style="text-align: right;;">05/03/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">70</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765232</td><td style="text-align: right;;">110131794</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">71</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765288</td><td style="text-align: right;;">110131850</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">2,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 2,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">72</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765292</td><td style="text-align: right;;">110131854</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">3,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 3,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">73</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765294</td><td style="text-align: right;;">110131856</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">74</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765309</td><td style="text-align: right;;">110131871</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">75</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765320</td><td style="text-align: right;;">110131882</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">76</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765340</td><td style="text-align: right;;">110131902</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">13/03/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">77</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765355</td><td style="text-align: right;;">110131917</td><td style="text-align: right;;">15/03/2010</td><td style="text-align: right;;">15/03/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">78</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765391</td><td style="text-align: right;;">110132005</td><td style="text-align: right;;">16/03/2010</td><td style="text-align: right;;">16/03/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">79</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765543</td><td style="text-align: right;;">110132290</td><td style="text-align: right;;">23/03/2010</td><td style="text-align: right;;">23/03/2010</td><td style="text-align: right;;">2,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 2,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">80</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765544</td><td style="text-align: right;;">110132291</td><td style="text-align: right;;">23/03/2010</td><td style="text-align: right;;">23/03/2010</td><td style="text-align: right;;">2,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 2,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">81</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110765546</td><td style="text-align: right;;">110132293</td><td style="text-align: right;;">23/03/2010</td><td style="text-align: right;;">23/03/2010</td><td style="text-align: right;;">2,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 2,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">82</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110766401</td><td style="text-align: right;;">110134451</td><td style="text-align: right;;">21/05/2010</td><td style="text-align: right;;">21/05/2010</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"> -1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">83</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110766402</td><td style="text-align: right;;">110134452</td><td style="text-align: right;;">21/05/2010</td><td style="text-align: right;;">21/05/2010</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"> -1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">84</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110766403</td><td style="text-align: right;;">110134454</td><td style="text-align: right;;">21/05/2010</td><td style="text-align: right;;">21/05/2010</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"> -1,000 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">85</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116030</td><td style="text-align: right;;">110719684</td><td style="text-align: right;;">110141015</td><td style="text-align: right;;">30/10/2010</td><td style="text-align: right;;">28/10/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style="background-color: #FFFF00;;">WIP</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">86</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116030</td><td style="text-align: right;;">110719686</td><td style="text-align: right;;">110141015</td><td style="text-align: right;;">30/10/2010</td><td style="text-align: right;;">28/10/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style="background-color: #FFFF00;;">WIP</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">87</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1106957</td><td style="text-align: right;;">116030</td><td style="text-align: right;;">110719688</td><td style="text-align: right;;">110141015</td><td style="text-align: right;;">30/10/2010</td><td style="text-align: right;;">28/10/2010</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,000 </td><td style="background-color: #FFFF00;;">WIP</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />
 
Last edited:

Gerald Higgins

Well-known Member
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

Board Regular
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.
 

Miratshah

Board Regular
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.

[HR][/HR]
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Company No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Company Code</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Job No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Account No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Trans. No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Journal No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Date Posted</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Entry Date</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Debits</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Credits</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;"> Balance in Base </td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6 ;;">Manual</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">90</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1104229</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110717373</td><td style="text-align: right;;">110126321</td><td style="text-align: right;;">25/09/2009</td><td style="text-align: right;;">25/09/2009</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">1,537.00</td><td style="text-align: right;;"> -1,537 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">91</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1104229</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110717373</td><td style="text-align: right;;">110126321</td><td style="text-align: right;;">25/09/2009</td><td style="text-align: right;;">25/09/2009</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">1,500.00</td><td style="text-align: right;;"> -1,500 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">92</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1104229</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110759137</td><td style="text-align: right;;">110116870</td><td style="text-align: right;;">13/01/2009</td><td style="text-align: right;;">13/01/2009</td><td style="text-align: right;;">1,537.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,537 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">93</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1104229</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110759352</td><td style="text-align: right;;">110117534</td><td style="text-align: right;;">04/02/2009</td><td style="text-align: right;;">04/02/2009</td><td style="text-align: right;;">1,500.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 1,500 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">769</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1104229</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110121160</td><td style="text-align: right;;">110200830</td><td style="text-align: right;;">10/12/2015</td><td style="text-align: right;;">10/12/2015</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">1,537.00</td><td style="text-align: right;;"> -1,537 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">770</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1104229</td><td style="text-align: right;;">116005</td><td style="text-align: right;;">110121160</td><td style="text-align: right;;">110200830</td><td style="text-align: right;;">10/12/2015</td><td style="text-align: right;;">10/12/2015</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">1,500.00</td><td style="text-align: right;;"> -1,500 </td><td style=";">CONTRA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">771</td><td style="text-align: right;;">110</td><td style=";">IN9073</td><td style="text-align: right;;">1104229</td><td style="text-align: right;;">116030</td><td style="text-align: right;;">110731339</td><td style="text-align: right;;">110201224</td><td style="text-align: right;;">29/12/2015</td><td style="text-align: right;;">28/12/2015</td><td style="text-align: right;;">3,037.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"> 3,037 </td><td style=";">CONTRA</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />
 

Gerald Higgins

Well-known Member
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

Board Regular
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

Well-known Member
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

Board Regular
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

Board Regular
Hi Gerald,

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

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;">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>
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top