# Thread: Excel Formula/VBA-Conditional Sum to find contra from multiple entries Thanks:  1 Post #5227673 (1) Likes: 0

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

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

ABCDEFGHIJKL
1Company No.Company CodeJob No.Account No.Trans. No.Journal No.Date PostedEntry DateDebitsCredits Balance in Base Manual
58110IN9073110695711600511012100111014223830/11/201030/11/20101,000.000.00 1,000 CONTRA
59110IN9073110695711600511012100111014223830/11/201030/11/20102,000.000.00 2,000 CONTRA
60110IN9073110695711600511015472411013261731/03/201029/03/20100.0038,500.00 -38,500 CONTRA
61110IN9073110695711600511076457711013045308/02/201008/02/20101,000.000.00 1,000 CONTRA
62110IN9073110695711600511076458511013046208/02/201008/02/20101,000.000.00 1,000 CONTRA
63110IN9073110695711600511076461611013049408/02/201008/02/20101,000.000.00 1,000 CONTRA
64110IN9073110695711600511076464011013052408/02/201008/02/20101,000.000.00 1,000 CONTRA
65110IN9073110695711600511076468511013057809/02/201009/02/20101,000.000.00 1,000 CONTRA
66110IN9073110695711600511076472011013062710/02/201010/02/20101,000.000.00 1,000 CONTRA
67110IN9073110695711600511076490511013104322/02/201022/02/20101,000.000.00 1,000 CONTRA
68110IN9073110695711600511076491011013105722/02/201022/02/201012,500.000.00 12,500 CONTRA
69110IN9073110695711600511076511911013157805/03/201005/03/20101,000.000.00 1,000 CONTRA
70110IN9073110695711600511076523211013179413/03/201013/03/20101,000.000.00 1,000 CONTRA
71110IN9073110695711600511076528811013185013/03/201013/03/20102,000.000.00 2,000 CONTRA
72110IN9073110695711600511076529211013185413/03/201013/03/20103,000.000.00 3,000 CONTRA
73110IN9073110695711600511076529411013185613/03/201013/03/20101,000.000.00 1,000 CONTRA
74110IN9073110695711600511076530911013187113/03/201013/03/20101,000.000.00 1,000 CONTRA
75110IN9073110695711600511076532011013188213/03/201013/03/20101,000.000.00 1,000 CONTRA
76110IN9073110695711600511076534011013190213/03/201013/03/20101,000.000.00 1,000 CONTRA
77110IN9073110695711600511076535511013191715/03/201015/03/20101,000.000.00 1,000 CONTRA
78110IN9073110695711600511076539111013200516/03/201016/03/20101,000.000.00 1,000 CONTRA
79110IN9073110695711600511076554311013229023/03/201023/03/20102,000.000.00 2,000 CONTRA
80110IN9073110695711600511076554411013229123/03/201023/03/20102,000.000.00 2,000 CONTRA
81110IN9073110695711600511076554611013229323/03/201023/03/20102,000.000.00 2,000 CONTRA
82110IN9073110695711600511076640111013445121/05/201021/05/20100.001,000.00 -1,000 CONTRA
83110IN9073110695711600511076640211013445221/05/201021/05/20100.001,000.00 -1,000 CONTRA
84110IN9073110695711600511076640311013445421/05/201021/05/20100.001,000.00 -1,000 CONTRA
85110IN9073110695711603011071968411014101530/10/201028/10/20101,000.000.00 1,000 WIP
86110IN9073110695711603011071968611014101530/10/201028/10/20101,000.000.00 1,000 WIP
87110IN9073110695711603011071968811014101530/10/201028/10/20101,000.000.00 1,000 WIP

Sheet1

2. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

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.

3. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

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

4. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

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

5. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Originally Posted by Gerald Higgins
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.

ABCDEFGHIJKL
1#9BC2E6 ;;">Company No.#9BC2E6 ;;">Company Code#9BC2E6 ;;">Job No.#9BC2E6 ;;">Account No.#9BC2E6 ;;">Trans. No.#9BC2E6 ;;">Journal No.#9BC2E6 ;;">Date Posted#9BC2E6 ;;">Entry Date#9BC2E6 ;;">Debits#9BC2E6 ;;">Credits#9BC2E6 ;;"> Balance in Base #9BC2E6 ;;">Manual
90110IN9073110422911600511071737311012632125/09/200925/09/20090.001,537.00 -1,537 CONTRA
91110IN9073110422911600511071737311012632125/09/200925/09/20090.001,500.00 -1,500 CONTRA
92110IN9073110422911600511075913711011687013/01/200913/01/20091,537.000.00 1,537 CONTRA
93110IN9073110422911600511075935211011753404/02/200904/02/20091,500.000.00 1,500 CONTRA
769110IN9073110422911600511012116011020083010/12/201510/12/20150.001,537.00 -1,537 CONTRA
770110IN9073110422911600511012116011020083010/12/201510/12/20150.001,500.00 -1,500 CONTRA
771110IN9073110422911603011073133911020122429/12/201528/12/20153,037.000.00 3,037 CONTRA

Sheet1

6. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

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.

7. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

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.

8. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

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.

9. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

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.

10. ## Re: Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Hi Gerald,

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

Code:
````Function``GetCombination(CoinsRange ``As``Range, SumCellId ``As``Double``) ``As``String``    ``Dim` `xStr ``As` `String`
`    ``Dim` `xSum ``As` `Double`
`    ``Dim` `xCell ``As` `Range`
`    ``xSum = SumCellId`
`    ``For` `Each` `xCell ``In` `CoinsRange`
`        ``If` `Not` `(xSum / xCell < 1) ``Then`
`            ``xStr = xStr & Int(xSum / xCell) & ``" of "` `& xCell & ``"  "`
`            ``xSum = xSum - (Int(xSum / xCell)) * xCell`
`        ``End` `If`
`    ``Next`
`    ``GetCombination = xStr`
`End``Function````