Page 4 of 9 FirstFirst ... 23456 ... LastLast
Results 31 to 40 of 83

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

  1. #31
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 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
    Here you go:-

    https://drive.google.com/open?id=1vd...gSH9xbp233r9rN

    I have added desired result in column O for reference. This was plotted manually for remaining transaction where total by Job (SumIF) did not return 0.
    Ok, I corrected the flaw in my previous code.
    Note:
    Using data in your workbook:
    I can see that when the total of each section by Job (SumIF) (and let’s call it “the residual”) is near zero (in your example 1 or 3) then it is considered zero. So what is the limit here? In this code below I'm using 5. It means that if the residual is off ± 5 from zero then it’s considered zero. You may change the limit in this line:
    Code:
    LMT = 5 'LMT is the limit (plus & minus) where you considered the sub total of each data section to be the same as zero.
    As I explained before the section with X (in column N) is where the code can't find the solution.
    I think I have an idea to improve the code to deal with the X marked part so we can find a better solution, let me now if you’re interested. But for now see if the code below suit you.


    Code:
    Sub a1086996d()
    'https://www.mrexcel.com/forum/excel-questions/1086996-excel-formula-vba-conditional-sum-find-contra-multiple-entries.html
    Dim i As Long, j As Long, n As Long
    Dim x As Long, k As Long, z As Long
    Dim q As Long, LMT As Long
    Dim va, vb, vc
    Dim flag As Boolean
    
    Application.ScreenUpdating = False
    
    'Change this to suit
    LMT = 5 'LMT is the limit (plus & minus) where you considered the sub total of each data section to be the same as zeru
    
    n = Range("C" & Rows.count).End(xlUp).Row
    va = Range("C1:C" & n)
    vb = Range("K1:K" & n)
    ReDim vc(1 To n, 1 To 3)
    Range("L1:N" & n).ClearContents
    
    
    For i = 2 To UBound(va, 1)
        j = i:  x = 0
        
        Do
            x = x + vb(i, 1)
            i = i + 1
            If i > UBound(va, 1) Then Exit Do
        Loop While va(i, 1) = va(i - 1, 1)
            
        i = i - 1
        
        vc(i, 2) = x
        flag = False
        
        If x >= -LMT And x <= LMT Then flag = True: GoTo skip:
        
        If x < 0 Then
            For k = j To i
                vb(k, 1) = vb(k, 1) * -1
            Next
            x = x * -1
        End If
        
                For k = j To i
                
                    z = 0
                    
                    For q = j To k
                    z = z + vb(q, 1)
                    Next
                    
                    flag = False
                    
                        If z = x Then
                            vc(k, 1) = 1: vc(k, 1) = "WIP": flag = True: GoTo skip:
                        ElseIf z > x Then
                            vb(k, 1) = 0
                        Else
                            If vb(k, 1) <= 0 Then
                                vb(k, 1) = 0
                            Else
                                vc(k, 1) = "WIP"
                            End If
                        End If
                        
                Next
            
    skip:
    
            If flag = False Then
                For k = j To i
                    vc(k, 3) = "X"
                Next
            Else
                For k = j To i
                    If vc(k, 1) <> "WPI" Then vc(k, 1) = "CONTRA"
                Next
    
            End If
    Next
    
    Range("L1").Resize(UBound(vc, 1), 3) = vc
    Range("L1") = "Manual"
    
    Application.ScreenUpdating = True
    End Sub

  2. #32
    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

    Please find below few observations on new code:-

    1. Logically if here is X in column O, there should not be tag in column L correct? This is since code could not identify whether its Contra or WIP.
    2. If there is only 1 transaction in a job, it is still marked as contra. It should become WIP since there is no corresponding transaction to sum to 0.
    3. If I total all Contra marked by the code, It is not summing to 0. Contra should sum to 0.
    4. There are few transactions marked as WIP however they are actually Contra. Very few though


    Quote Originally Posted by Akuini View Post
    As I explained before the section with X (in column N) is where the code can't find the solution.
    I think I have an idea to improve the code to deal with the X marked part so we can find a better solution, let me now if you’re interested. But for now see if the code below suit you.
    I would love to know a better solution.

  3. #33
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 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
    Please find below few observations on new code:-
    1. Logically if here is X in column O, there should not be tag in column L correct? This is since code could not identify whether its Contra or WIP.
    Yes, but actually it doesnt matter, because if there is X it means you must ignore the result in col L because it's wrong. If it's kind of confusing I can put the X in col L, so it will automatically delete all the wrong result in there.

    2. If there is only 1 transaction in a job, it is still marked as contra. It should become WIP since there is no corresponding transaction to sum to 0.
    Actually that's what the code does, for example row 264. In what row you found it marked as contra?

    3. If I total all Contra marked by the code, It is not summing to 0. Contra should sum to 0.
    You meant all Contra in whole data (not just a section)? well, it is not summing to 0, since you have problem with rounding number at the first place. For example the residual at row 18 (first section) is -1, but you manually mark all entries as Contra, and that's because you consider -1 as 0, don't you?. That's why I asked you about the limit.

    4. There are few transactions marked as WIP however they are actually Contra. Very few though
    In what row? I need to check that.

  4. #34
    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
    1. Logically if here is X in column O, there should not be tag in column L correct? This is since code could not identify whether its Contra or WIP.
    Yes, but actually it doesnt matter, because if there is X it means you must ignore the result in col L because it's wrong. If it's kind of confusing I can put the X in col L, so it will automatically delete all the wrong result in there.
    Okay. You can do that.

    Quote Originally Posted by Akuini View Post
    2. If there is only 1 transaction in a job, it is still marked as contra. It should become WIP since there is no corresponding transaction to sum to 0.
    Actually that's what the code does, for example row 264. In what row you found it marked as contra?
    Row 264 for Job 8111863 for amount 62,500 is marked as Contra. Similarly Job numbers 8111939, 8111958 and 8111773-13. All have just one line however marked as Contra.

    Quote Originally Posted by Akuini View Post
    3. If I total all Contra marked by the code, It is not summing to 0. Contra should sum to 0.
    You meant all Contra in whole data (not just a section)? well, it is not summing to 0, since you have problem with rounding number at the first place. For example the residual at row 18 (first section) is -1, but you manually mark all entries as Contra, and that's because you consider -1 as 0, don't you?. That's why I asked you about the limit.
    I changed the limit to 10 from 5. Post that If I select only Contra marked transactions, I get total of 95,032 due to Jobs mentioned in point number 2.

    Quote Originally Posted by Akuini View Post
    4. There are few transactions marked as WIP however they are actually Contra. Very few though
    In what row? I need to check that.
    I think once you execute point 1, WIP will be wiped off with X. So we can ignore this. All WIP for this point are also marked as X.
    Last edited by Miratshah; Feb 14th, 2019 at 05:33 AM.

  5. #35
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 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
    Okay. You can do that.


    Row 264 for Job 8111863 for amount 62,500 is marked as Contra. Similarly Job numbers 8111939, 8111958 and 8111773-13. All have just one line however marked as Contra.


    I changed the limit to 10 from 5. Post that If I select only Contra marked transactions, I get total of 95,032 due to Jobs mentioned in point number 2.


    I think once you execute point 1, WIP will be wiped off with X. So we can ignore this. All WIP for this point are also marked as X.
    Ok, try this:
    Code:
    Sub a1086996e()
    'https://www.mrexcel.com/forum/excel-questions/1086996-excel-formula-vba-conditional-sum-find-contra-multiple-entries.html
    Dim i As Long, j As Long, n As Long
    Dim x As Long, k As Long, z As Long
    Dim q As Long, LMT As Long
    Dim va, vb, vc
    Dim flag As Boolean, oneline As Boolean
     
    Application.ScreenUpdating = False
     
    'Change this to suit
    LMT = 10 'LMT is the limit (plus & minus) where you considered the sub total of each data section to be the same as zero
     
    n = Range("C" & Rows.count).End(xlUp).Row
    va = Range("C1:C" & n)
    vb = Range("K1:K" & n)
    ReDim vc(1 To n, 1 To 3)
    Range("L1:N" & n).ClearContents
     
     
    For i = 2 To UBound(va, 1)
        j = i:  x = 0
       
     
        Do
            x = x + vb(i, 1)
            i = i + 1
            If i > UBound(va, 1) Then Exit Do
        Loop While va(i, 1) = va(i - 1, 1)
           
        i = i - 1
       
        vc(i, 2) = x
           
            flag = False
        If x >= -LMT And x <= LMT Then flag = True: GoTo skip:
       
            oneline = False
        If j = i Then oneline = True: flag = True: GoTo skip:
     
       
        If x < 0 Then
            For k = j To i
                vb(k, 1) = vb(k, 1) * -1
            Next
            x = x * -1
        End If
       
                For k = j To i
               
                    z = 0
                   
                    For q = j To k
                    z = z + vb(q, 1)
                    Next
                   
                    flag = False
                   
                        If z = x Then
                            vc(k, 1) = 1: vc(k, 1) = "WIP": flag = True: GoTo skip:
                        ElseIf z > x Then
                            vb(k, 1) = 0
                        Else
                            If vb(k, 1) <= 0 Then
                                vb(k, 1) = 0
                            Else
                                vc(k, 1) = "WIP"
                            End If
                        End If
                       
                Next
           
    skip:
     
            If flag = False Then
                For k = j To i
                    vc(k, 1) = "X"
                Next
            Else
                For k = j To i
                    If vc(k, 1) <> "WIP" Then vc(k, 1) = "CONTRA": flag = False
                   If oneline = True Then oneline = False: vc(k, 1) = "WIP"
                Next
     
            End If
    Next
     
    Range("L1").Resize(UBound(vc, 1), 3) = vc
    Range("L1") = "By Macro"
     
    Application.ScreenUpdating = True
    End Sub

  6. #36
    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

    Okay this looks much better. This code identifies all contra where SUMIF for those jobs would result in either 0 or under 10. Also identifies WIP where there is just one transaction for a job.

    Is there a way to mark following transactions Contra since they are same amount with different signs within same job number?

    For example:-
    Job number - 8111876
    Row number - 265, 266, 267, 268, 269, 271, 272, 274, 275, 276, 277, 278

    If not, can we identify Row number 270, 273 and 278 as WIP because their is the total of Job number as mentioned in column M?

    As of now, All transactions in this job are marked as X and this is the case where Job total is beyond our rounding limit of 10.

  7. #37
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 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
    Okay this looks much better. This code identifies all contra where SUMIF for those jobs would result in either 0 or under 10. Also identifies WIP where there is just one transaction for a job.

    Is there a way to mark following transactions Contra since they are same amount with different signs within same job number?

    For example:-
    Job number - 8111876
    Row number - 265, 266, 267, 268, 269, 271, 272, 274, 275, 276, 277, 278

    If not, can we identify Row number 270, 273 and 278 as WIP because their is the total of Job number as mentioned in column M?

    As of now, All transactions in this job are marked as X and this is the case where Job total is beyond our rounding limit of 10.
    I"ll see what I can do, I'll be back tomorrow when I have time.

  8. #38
    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
    I"ll see what I can do, I'll be back tomorrow when I have time.
    Sure. I will be waiting. I am gaining some confidence that you will be able to crack the logic.

  9. #39
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 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
    Sure. I will be waiting. I am gaining some confidence that you will be able to crack the logic.
    I modified my code to get better result. I also use & modified a code that originally is written by John Coleman:
    https://stackoverflow.com/questions/...-a-list-in-vba

    Note:

    The code is in “Module1”.
    Run “Sub findWIP”, it will run “Sub removeMatch()” & “Function ListSubsets1”.
    Run “Sub checksum”. It will generate the sub total of WIP in each section in column N & format the cell with WIP with brown.
    If you want each section have alternate color then run “Sub toColor()”.



    The workbook:
    https://www.dropbox.com/s/uoke6why38...ries.xlsm?dl=0

  10. #40
    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
    I modified my code to get better result. I also use & modified a code that originally is written by John Coleman:
    https://stackoverflow.com/questions/...-a-list-in-vba
    This is very humble of you give credit.

    Quote Originally Posted by Akuini View Post
    Note:

    The code is in “Module1”.
    Run “Sub findWIP”, it will run “Sub removeMatch()” & “Function ListSubsets1”.
    Run “Sub checksum”. It will generate the sub total of WIP in each section in column N & format the cell with WIP with brown.
    If you want each section have alternate color then run “Sub toColor()”.



    The workbook:
    https://www.dropbox.com/s/uoke6why38...ries.xlsm?dl=0
    I checked it for a smaller data size with just 1 job number and it ran perfectly fine. The only concern is, it takes huge amount of time to run over even 100 rows. I am wondering will I be able to run this over 30000 rows.

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
  •