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

M

Miratshah

Board Regular
Joined
Nov 29, 2016
Messages
57
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
ABCDEFGHIJKL
1Company No.Company CodeJob No.Account No.Trans. No.Journal No.Date PostedEntry DateDebitsCreditsBalance in BaseManual
58110IN9073110695711600511012100111014223830/11/201030/11/20101,000.000.001,000CONTRA
59110IN9073110695711600511012100111014223830/11/201030/11/20102,000.000.002,000CONTRA
60110IN9073110695711600511015472411013261731/03/201029/03/20100.0038,500.00-38,500CONTRA
61110IN9073110695711600511076457711013045308/02/201008/02/20101,000.000.001,000CONTRA
62110IN9073110695711600511076458511013046208/02/201008/02/20101,000.000.001,000CONTRA
63110IN9073110695711600511076461611013049408/02/201008/02/20101,000.000.001,000CONTRA
64110IN9073110695711600511076464011013052408/02/201008/02/20101,000.000.001,000CONTRA
65110IN9073110695711600511076468511013057809/02/201009/02/20101,000.000.001,000CONTRA
66110IN9073110695711600511076472011013062710/02/201010/02/20101,000.000.001,000CONTRA
67110IN9073110695711600511076490511013104322/02/201022/02/20101,000.000.001,000CONTRA
68110IN9073110695711600511076491011013105722/02/201022/02/201012,500.000.0012,500CONTRA
69110IN9073110695711600511076511911013157805/03/201005/03/20101,000.000.001,000CONTRA
70110IN9073110695711600511076523211013179413/03/201013/03/20101,000.000.001,000CONTRA
71110IN9073110695711600511076528811013185013/03/201013/03/20102,000.000.002,000CONTRA
72110IN9073110695711600511076529211013185413/03/201013/03/20103,000.000.003,000CONTRA
73110IN9073110695711600511076529411013185613/03/201013/03/20101,000.000.001,000CONTRA
74110IN9073110695711600511076530911013187113/03/201013/03/20101,000.000.001,000CONTRA
75110IN9073110695711600511076532011013188213/03/201013/03/20101,000.000.001,000CONTRA
76110IN9073110695711600511076534011013190213/03/201013/03/20101,000.000.001,000CONTRA
77110IN9073110695711600511076535511013191715/03/201015/03/20101,000.000.001,000CONTRA
78110IN9073110695711600511076539111013200516/03/201016/03/20101,000.000.001,000CONTRA
79110IN9073110695711600511076554311013229023/03/201023/03/20102,000.000.002,000CONTRA
80110IN9073110695711600511076554411013229123/03/201023/03/20102,000.000.002,000CONTRA
81110IN9073110695711600511076554611013229323/03/201023/03/20102,000.000.002,000CONTRA
82110IN9073110695711600511076640111013445121/05/201021/05/20100.001,000.00-1,000CONTRA
83110IN9073110695711600511076640211013445221/05/201021/05/20100.001,000.00-1,000CONTRA
84110IN9073110695711600511076640311013445421/05/201021/05/20100.001,000.00-1,000CONTRA
85110IN9073110695711603011071968411014101530/10/201028/10/20101,000.000.001,000WIP
86110IN9073110695711603011071968611014101530/10/201028/10/20101,000.000.001,000WIP
87110IN9073110695711603011071968811014101530/10/201028/10/20101,000.000.001,000WIP
Sheet1
 
Last edited:
Sort by date Sort by votes
Miratshah said:
So did it finish at your end? It did not finish at my end even for 4000 rows.

Yes, you can have a look for optimisation and revert back.

Thanks once again.
Click to expand...

Actually, I didn't get to finish it, when it run above 2 minute I terminated excel.
Then I run the earlier code from post #35 , and in seconds I get the solution for 23.000+ row, the rest, about 8300 rows, markes as X. Those the ones that make the latest code run so much longer.

One thing I want ask.
In the section that marked as X above, I found many entries that match as positive-negative pair but off 1 number.
For example:
2506263
-2506264
3445860
-3445861
I believe those are exact positive-negative pair in the original format (the decimal format).
What method did you use for rounding the number?
 
Last edited:
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I used INT formula to convert to integer. I can use any other better formula. Small change in values don't matter.
 
Upvote 0
Miratshah said:
I used INT formula to convert to integer. I can use any other better formula. Small change in values don't matter.
Click to expand...

OK, here’s what I got so far.
I use ROUND function to make the data becomes integer. It seems to resolve the issue with positive-negative pair that off 1 number as I mentioned earlier.
The code is in “Module1”.
Run “Sub findWIP”, it will run “Sub Combinations2 ()” & “Sub CombinationsNP2”.
I used & modified the last 2 codes that is written by MickG:
https://www.ozgrid.com/forum/forum/...-all-possible-combinations-from-single-column
If you want each section have alternate color then run “Sub toColor()”.
Run “Sub checksum”. It will generate the sub total of WIP in each section in column N & the sub total of CONTRA right above it.

What the code does:
Note:
Section means data grouped by Job number
Residual means the subtotal of col K in a section

The process has 2 stages:
Stage 1:
- if the residual falls between the rounding limit then all items will be marked as Contra
- if a section only has 1 item then the item will be marked as WIP
- if there is 1 item that match the residual then mark it as WIP & the rest as Contra.
So if a section do not meet one of the above condition then it continues to Stage 2.

Stage 2:
The will find combination of items that sum to 0 and then mark the items as Contra.
The previous code is set to find the solution by using all possible combination of all items in the section. That’s why it could take so long to finish. So I revised the code by limiting the number of combination up to 4 & if the number of items in a section is more than 50 then it will limit the number of combination to 2. You may change this setting in this part:

The result:
By Macro - Residual - Stage - Sum WIP & Contra
Column L (“By Macro”): WIP & MACRO
Column M (“Residual”): sums of each section in column K
Column N (“Stage”): stage 1 means that section only need stage 1 process. Stage 2 means that section need stage 2 process.
Column O (“Sum WIP & Contra”): sum of WIP in a section and above it, is sum of Contra.
Sum of WIP must be the same with column M. Sum of Contra must be ) or within the limit of rounding number (i.e ± 10).

The workbook:
https://www.dropbox.com/s/iv2e60i1b...al-sum-find-contra-multiple-entries.xlsb?dl=0
 
Upvote 0
Sorry, forgot this part:

So I revised the code by limiting the number of combination up to 4 & if the number of items in a section is more than 50 then it will limit the number of combination to 2. You may change this setting in this part:
You may change mx & px value:
Code: 
            'SETTING. Change to suit
            mx = 50 'maximum number of item in a section
            px = 4  'maximum number of combination
            ba = bb - aa
            If ba >= mx Then px = 2
                If ba + 1 < px Then px = ba + 1
                    Call Combinations2(px)
            End If
 
Upvote 0
So when I ran Sub FindWIP(), a pop up came - Total time for processing - 10 seconds.

This was the happiest moment of 2019.

Kudos to you for such a marvelous job done. Amazing !

A Big Thank you! :)
 
Upvote 0
Miratshah said:
So when I ran Sub FindWIP(), a pop up came - Total time for processing - 10 seconds.

This was the happiest moment of 2019.

Kudos to you for such a marvelous job done. Amazing !

A Big Thank you! :)
Click to expand...

You're welcome, glad to help, & thanks for the feedback.:)

Ok, the speed isn't a problem now, but have you checked the result? Is it as you expected?
 
Upvote 0
Akuini said:
You're welcome, glad to help, & thanks for the feedback.:)

Ok, the speed isn't a problem now, but have you checked the result? Is it as you expected?
Click to expand...


Yes I checked the result and there are some gaps however they can be managed. If my team suggests to add another identifier along with Job number, how difficult of a task it is? I.e. Account number in column D.
 
Upvote 0
Miratshah said:
Yes I checked the result and there are some gaps however they can be managed. If my team suggests to add another identifier along with Job number, how difficult of a task it is? I.e. Account number in column D.
Click to expand...

When i say gaps, it does not mean mistake. :)
 
Upvote 0
Miratshah said:
Yes I checked the result and there are some gaps however they can be managed.
Click to expand...

What kind of gaps?

If my team suggests to add another identifier along with Job number, how difficult of a task it is? I.e. Account number in column D.
Click to expand...
That won't be too hard, I guess.
 
Upvote 0
Akuini said:
What kind of gaps?
Click to expand...

I was analyzing the result and observed Job number 1312119. There is an amount 23,124 marked as contra by Macro and WIP by Manual plotting and one more same amount with reverse marking. I checked the account number for both transactions, and feel manual plotting is more logical.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Conditional Quantity sum based on duplicate values in various other columns
Replies
3
Views
323
whassan
W
A
  • Question
To Find the difference of values through their crossing points on the Graph
Replies
2
Views
362
Fluff
Fluff
T
  • Question
SumIfs' formula with Multiple criteria including dates
Replies
3
Views
611
DanteAmor
DanteAmor
N
  • Question
First in First Out (Fifo) Stock balance Calaculation File.
Replies
2
Views
8K
TotallyConfused
T
D
  • Question
Sum Product or Sum IF for criteria in multiple columns
Replies
1
Views
412
cerfani
C

Forum statistics

Threads
1,214,646
Messages
6,120,716
Members
448,985
Latest member
chocbudda

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top