Page 2 of 9 FirstFirst 1234 ... LastLast
Results 11 to 20 of 83

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

  1. #11
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    I don't know.
    I'm not a code expert and I have not used Solver very much.

    There are threads on this board which address similar problems, about having some number of transactions and having to net some of them off against each other.
    Try searching for them.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  2. #12
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

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

    Hi, Miratshah
    Question:
    1. Your data in column K, are they double type/decimal or just integer?
    If they are decimal then I think it's hard to find a solution.

    P.s. Rounding off to 1 or 2 is completely allowed
    That's what bothers me.


    2. If they are integer, how about simplifying the logic, like this:
    Using your example in post #1 , we can easily find that for that particular company & job the subtotal in col K is 3000. Using vba, why not just looping in col K until we find some entry which subtotal is 3000, then mark those entries as "WIP" and the rest will be "CONTRA". If we loop from the top then we will mark row 58-59 as WIP or if we loop from the bottom we will mark row 85-87 as WIP.

  3. #13
    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
    Hi, Miratshah
    Question:
    1. Your data in column K, are they double type/decimal or just integer?
    If they are decimal then I think it's hard to find a solution.


    That's what bothers me.


    2. If they are integer, how about simplifying the logic, like this:
    Using your example in post #1 , we can easily find that for that particular company & job the subtotal in col K is 3000. Using vba, why not just looping in col K until we find some entry which subtotal is 3000, then mark those entries as "WIP" and the rest will be "CONTRA". If we loop from the top then we will mark row 58-59 as WIP or if we loop from the bottom we will mark row 85-87 as WIP.
    Hi Akuini,

    What I can do is, before starting my work, I can convert all decimal to integer by using round off formula. This is not an issue.

    I believe point 2 in your post is a wonderful idea. However, can the VBA you are suggesting work in loop for each company code by Job? Please note that amounts will not always be straight forward + or -.

    I am now little optimistic that solution is not too far.

    Thanks.
    Last edited by Miratshah; Feb 13th, 2019 at 02:26 AM.

  4. #14
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    35 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
    Hi Akuini,
    I believe point 2 in your post is a wonderful idea. However, can the VBA you are suggesting work in loop for each company code by Job? Please note that amounts will not always be straight forward + or -.
    Thanks.
    The Job No is unique for each company, right? I mean 1 company can have 2 or more Job No but 2 companies can’t have the same Job No, correct? For example: job no 1104229 is only for company IN9073
    So is it correct to assume that the data grouping for each analysis can be based on Job No only?
    Last edited by Akuini; Feb 13th, 2019 at 02:57 AM.

  5. #15
    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
    The Job No is unique for each company, right? I mean 1 company can have 2 or more Job No but 2 companies can’t have the same Job No, correct? For example: job no 1104229 is only for company IN9073
    So is it correct to assume that the data grouping for each analysis can be based on Job No only?
    Yes, It is safe to assume that the data grouping for each analysis can be based on Job number only.

  6. #16
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

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

    Ok, try this:
    I use a mock-up data to test the code.
    The code only marks the WPI, the blank means they are CONTRA.
    In col M the code add the sub total of each data group.

    Code:
    Sub a1086996b()
    '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
    Dim va, vb, vc
    
    Application.ScreenUpdating = False
    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 2)
    Range("L1:M" & 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
        
        If x = 0 Then 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
                    
                    Debug.Print z
                        
                        If z = x Then
                            vc(k, 1) = 1: vc(k, 1) = "WPI": 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) = "WPI"
                            End If
                        End If
                        
                Next
            
    skip:
    
    Next
    
    Range("L1").Resize(UBound(vc, 1), 2) = vc
    Range("L1") = "Manual"
    
    Application.ScreenUpdating = True
    End Sub

    RESULT:

    Excel 2013 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Company No. Company Code Job No. Account No. Trans. No. Journal No. Date Posted Entry Date Debits Credits Balance in Base Manual
    2
    AA
    101
    1
    WPI
    3
    AA
    101
    1
    WPI
    4
    AA
    101
    -3
    5
    AA
    101
    2
    6
    AA
    101
    -1
    7
    AA
    101
    1
    WPI
    8
    AA
    101
    2
    3
    9
    AA
    102
    -3
    10
    AA
    102
    1
    11
    AA
    102
    -1
    12
    AA
    102
    1
    13
    AA
    102
    -1
    14
    AA
    102
    1
    15
    AA
    102
    2
    0
    16
    AD
    302
    3
    WPI
    17
    AD
    302
    1
    WPI
    18
    AD
    302
    -1
    19
    AD
    302
    1
    20
    AD
    302
    -1
    21
    AD
    302
    1
    4
    22
    AD
    501
    2
    23
    AD
    501
    1
    24
    AD
    501
    -1
    WPI
    25
    AD
    501
    1
    26
    AD
    501
    -2
    WPI
    27
    AD
    501
    3
    28
    AD
    501
    1
    29
    AD
    501
    -1
    WPI
    30
    AD
    501
    -3
    31
    AD
    501
    -5
    -4
    Sheet: Sheet1

  7. #17
    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 I tried this code in a bigger sample. I am happy to report that it works for the most part however there are places where I found incorrect results. I sorted Job numbers in ascending order so that all job numbers are one after the other, this reduced number of errors for sure. However still there are few places with incorrect results. Column N is where manual contra & WIP are ploted against each transactions and column O is where I am comparing the result if VBA to manual. Sorry for the delay, unfortunately complete HTML was not getting uploaded hence reduced few rows.


    ABCDEFGHIJKLMNO
    1Company No.Company codeJob No.Account No.Trans. No.Journal No.Date PostedEntry DateDebitsCreditsBalance in BaseManualSumIFByJobManualPlottingCheck
    28111821787.00WIPCONTRAFALSE
    381118211,021.00WIPCONTRAFALSE
    481118211,176.00WIPCONTRAFALSE
    581118211,078.00WIPCONTRAFALSE
    681118219,69,650.00CONTRAWIPFALSE
    78111821-4,062.00CONTRA9,69,650.00CONTRATRUE
    8811187618,136.00WIPWIPTRUE
    981118761,50,000.00CONTRAWIPFALSE
    1081118762,07,761.00CONTRAWIPFALSE
    118111876-18,136.00CONTRACONTRATRUE
    12811187618,136.00WIPCONTRAFALSE
    138111876-1,25,181.00CONTRACONTRATRUE
    148111876-1,50,000.00CONTRACONTRATRUE
    1581118761,25,181.00CONTRACONTRATRUE
    1681118761,50,000.00CONTRACONTRATRUE
    178111876-1,25,181.00CONTRACONTRATRUE
    188111876-1,50,000.00CONTRACONTRATRUE
    1981118761,25,181.00CONTRACONTRATRUE
    2081118761,50,000.00CONTRACONTRATRUE
    218111876-1,25,181.00CONTRAWIPFALSE
    228111876-1,50,000.00CONTRA1,00,716.00WIPFALSE
    23811191026,430.00WIPWIPTRUE
    2481119105,915.00WIPWIPTRUE
    2581119106,05,234.00CONTRAWIPFALSE
    2681119103,08,648.00WIPWIPTRUE
    2781119101,75,824.00CONTRAWIPFALSE
    28811191043,952.00CONTRAWIPFALSE
    298111910-32,346.00CONTRACONTRATRUE
    30811191032,346.00CONTRACONTRATRUE
    318111910-6,37,580.00CONTRACONTRATRUE
    3281119106,37,580.00CONTRACONTRATRUE
    338111910-11,66,005.00CONTRACONTRATRUE
    3481119105,50,000.00CONTRAWIPFALSE
    35811191011,66,005.00CONTRACONTRATRUE
    368111910-9,97,710.00CONTRAWIPFALSE
    378111910-3,50,000.00CONTRA3,68,293.00WIPFALSE

    Sheet1



    Worksheet Formulas
    CellFormula
    O2=N2=L2
    O3=N3=L3
    O4=N4=L4
    O5=N5=L5
    O6=N6=L6
    O7=N7=L7
    O8=N8=L8
    O9=N9=L9
    O10=N10=L10
    O11=N11=L11
    O12=N12=L12
    O13=N13=L13
    O14=N14=L14
    O15=N15=L15
    O16=N16=L16
    O17=N17=L17
    O18=N18=L18
    O19=N19=L19
    O20=N20=L20
    O21=N21=L21
    O22=N22=L22
    O23=N23=L23
    O24=N24=L24
    O25=N25=L25
    O26=N26=L26
    O27=N27=L27
    O28=N28=L28
    O29=N29=L29
    O30=N30=L30
    O31=N31=L31
    O32=N32=L32
    O33=N33=L33
    O34=N34=L34
    O35=N35=L35
    O36=N36=L36
    O37=N37=L37


  8. #18
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

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

    Sorry, after reading your last data I don’t think I can find a good solution for your problem. So maybe someone else here could help.
    But I added some lines in the code just to mark the section (with X) where the code can't find the solution, so at least you know where to look, to do it manually.
    Here’s the revised code:



    Code:
    Sub a1086996c()
    '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
    Dim va, vb, vc
    Dim flag As Boolean
    
    Application.ScreenUpdating = False
    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 2)
    Range("L1:M" & 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
        
        If x = 0 Then 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) = "WPI": 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) = "WPI"
                            End If
                        End If
                        
                Next
            
    skip:
    
            If flag = False Then
                For k = j To i
                    vc(k, 1) = "X"
                Next
            End If
    Next
    
    Range("L1").Resize(UBound(vc, 1), 2) = vc
    Range("L1") = "Manual"
    
    Application.ScreenUpdating = True
    End Sub
    Last edited by Akuini; Feb 13th, 2019 at 08:13 AM.

  9. #19
    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

    Yes, even that will save lot of time. However the code you just sent marked every transaction as X. Did something change in the code?

  10. #20
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    35 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, even that will save lot of time. However the code you just sent marked every transaction as X. Did something change in the code?

    Yes, as I said itís to mark the section (with X) where the code can't find the solution. For the section that the code can find the solution it will mark with WPI ( and blank).

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
  •