I used INT formula to convert to integer. I can use any other better formula. Small change in values don't matter.
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 by Akuini; Feb 15th, 2019 at 10:51 AM.
I used INT formula to convert to integer. I can use any other better formula. Small change in values don't matter.
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/h...-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/iv2e60i1b0...ries.xlsb?dl=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
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!
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 by Miratshah; Feb 18th, 2019 at 03:07 AM.
Like this thread? Share it with others