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

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

Originally Posted by Miratshah
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.
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?

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

I used INT formula to convert to integer. I can use any other better formula. Small change in values don't matter.

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

Originally Posted by Miratshah
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

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

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

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

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!

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

Originally Posted by Miratshah
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!
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?

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

Originally Posted by Akuini
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?

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.

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

Originally Posted by Miratshah
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.
When i say gaps, it does not mean mistake.

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

Originally Posted by Miratshah
Yes I checked the result and there are some gaps however they can be managed.
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.
That won't be too hard, I guess.

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

Originally Posted by Akuini
What kind of gaps?
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•