Page 6 of 9 FirstFirst ... 45678 ... LastLast
Results 51 to 60 of 83

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

  1. #51
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

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

    Quote Originally Posted by Miratshah View Post
    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?
    Last edited by Akuini; Feb 15th, 2019 at 10:51 AM.

  2. #52
    Board Regular
    Join Date
    Nov 2016
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #53
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

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

    Quote Originally Posted by Miratshah View Post
    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. #54
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default 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. #55
    Board Regular
    Join Date
    Nov 2016
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #56
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

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

    Quote Originally Posted by Miratshah View Post
    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. #57
    Board Regular
    Join Date
    Nov 2016
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Akuini View Post
    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. #58
    Board Regular
    Join Date
    Nov 2016
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Miratshah View Post
    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. #59
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

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

    Quote Originally Posted by Miratshah View Post
    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. #60
    Board Regular
    Join Date
    Nov 2016
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Akuini View Post
    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.
    Last edited by Miratshah; Feb 18th, 2019 at 03:07 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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