Page 3 of 9 FirstFirst 12345 ... LastLast
Results 21 to 30 of 83

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

  1. #21
    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
    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).
    But so many correct entries as per old codes are also marked X now. Infact all are marked X.

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

    Would it help if I after sorting the data by JOB number, I sort then by amount before running to code?

  3. #23
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,148
    Post Thanks / Like
    Mentioned
    43 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
    But so many correct entries as per old codes are also marked X now. Infact all are marked X.
    Are you sure?
    I change my example (in post 16#) a bit in the second & third section.
    The result is the second & third section are marked with X, but the rest is still mark with WPI & blank.


    Excel 2013 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Q Q Job No. Q Q Q Q Q Q Q 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
    X
    10
    AA
    102
    1
    X
    11
    AA
    102
    10
    X
    12
    AA
    102
    1
    X
    13
    AA
    102
    -1
    X
    14
    AA
    102
    -1
    X
    15
    AA
    102
    2
    X
    9
    16
    AD
    302
    3
    X
    17
    AD
    302
    1
    X
    18
    AD
    302
    10
    X
    19
    AD
    302
    1
    X
    20
    AD
    302
    -5
    X
    21
    AD
    302
    1
    X
    11
    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: Sheet4

  4. #24
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,148
    Post Thanks / Like
    Mentioned
    43 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
    Would it help if I after sorting the data by JOB number, I sort then by amount before running to code?
    I’m not sure but try something like this:
    Using my second code, after running the code, move all section with X mark to another sheet, then sort the data by col K ascending (off course sort by Job No still & always the first criteria), run the code again, repeat the step to move all section with X mark then try again sort the data by col K descending.

  5. #25
    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, Surprisingly.


    K
    3-2,26,800.00
    4-2,26,800.00

    Sheet5



    Worksheet Formulas
    CellFormula
    A1Company No.
    B1Company Code
    C1Job No.
    D1Account No.
    E1Trans. No.
    F1Journal No.
    G1Date Posted
    H1Entry Date
    I1Debits
    J1Credits
    K1Balance in Base
    L1ManualNewVBA
    M1SUMIFNewVBA
    N1ManualOldVBA
    O1SUMIFOldVBA
    A2
    B2
    C28111789
    D2
    E2
    F2
    G2
    H2
    I2
    J2
    K2-291819
    L2X
    M2
    N2CONTRA
    O2
    A3
    B3
    C38111789
    D3
    E3
    F3
    G3
    H3
    I3
    J3
    K3-226800
    L3X
    M3
    N3CONTRA
    O3
    A4
    B4
    C48111789
    D4
    E4
    F4
    G4
    H4
    I4
    J4
    K4-226800
    L4X
    M4
    N4CONTRA
    O4
    A5
    B5
    C58111789
    D5
    E5
    F5
    G5
    H5
    I5
    J5
    K5-226800
    L5X
    M5
    N5CONTRA
    O5
    A6
    B6
    C68111789
    D6
    E6
    F6
    G6
    H6
    I6
    J6
    K6-226800
    L6X
    M6
    N6CONTRA
    O6
    A7
    B7
    C78111789
    D7
    E7
    F7
    G7
    H7
    I7
    J7
    K7-226800
    L7X
    M7
    N7CONTRA
    O7
    A8
    B8
    C88111789
    D8
    E8
    F8
    G8
    H8
    I8
    J8
    K8-226800
    L8X
    M8
    N8CONTRA
    O8
    A9
    B9
    C98111789
    D9
    E9
    F9
    G9
    H9
    I9
    J9
    K9-226800
    L9X
    M9
    N9CONTRA
    O9
    A10
    B10
    C108111789
    D10
    E10
    F10
    G10
    H10
    I10
    J10
    K10-226800
    L10X
    M10
    N10CONTRA
    O10
    A11
    B11
    C118111789
    D11
    E11
    F11
    G11
    H11
    I11
    J11
    K11226800
    L11X
    M11
    N11CONTRA
    O11
    A12
    B12
    C128111789
    D12
    E12
    F12
    G12
    H12
    I12
    J12
    K12226800
    L12X
    M12
    N12CONTRA
    O12
    A13
    B13
    C138111789
    D13
    E13
    F13
    G13
    H13
    I13
    J13
    K13226800
    L13X
    M13
    N13CONTRA
    O13
    A14
    B14
    C148111789
    D14
    E14
    F14
    G14
    H14
    I14
    J14
    K14226800
    L14X
    M14
    N14CONTRA
    O14
    A15
    B15
    C158111789
    D15
    E15
    F15
    G15
    H15
    I15
    J15
    K15226800
    L15X
    M15
    N15CONTRA
    O15
    A16
    B16
    C168111789
    D16
    E16
    F16
    G16
    H16
    I16
    J16
    K16226800
    L16X
    M16
    N16CONTRA
    O16
    A17
    B17
    C178111789
    D17
    E17
    F17
    G17
    H17
    I17
    J17
    K17226800
    L17X
    M17
    N17CONTRA
    O17
    A18
    B18
    C188111789
    D18
    E18
    F18
    G18
    H18
    I18
    J18
    K18226800
    L18X
    M18
    N18CONTRA
    O18
    A19
    B19
    C198111789
    D19
    E19
    F19
    G19
    H19
    I19
    J19
    K19291818
    L19X
    M19-1
    N19CONTRA
    O19-1


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

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

    Not sure why that happened.
    Then try this, it combine the first & second code.
    The X mark will be placed in col N.

    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 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
        
        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, 3) = "X"
                Next
            End If
    Next
    
    Range("L1").Resize(UBound(vc, 1), 3) = vc
    Range("L1") = "Manual"
    
    Application.ScreenUpdating = True
    End Sub

  7. #27
    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
    Not sure why that happened.
    Then try this, it combine the first & second code.
    The X mark will be placed in col N.
    This gave all blank in column L and all X in column N.

  8. #28
    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

    Not trying to disrespect, is there any logic that we are missing in identifying transactions that sum to sumif of job number? At few places it did work as per 1st code.

  9. #29
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,148
    Post Thanks / Like
    Mentioned
    43 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
    This gave all blank in column L and all X in column N.
    Could you upload your workbook (without sensitive data) somewhere (maybe via dropbox.com or google drive)?
    Then put the link here. Just the data from col B & K would be enough.

    Not trying to disrespect, is there any logic that we are missing in identifying transactions that sum to sumif of job number? At few places it did work as per 1st code.
    I don't understand what you mean.

  10. #30
    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
    Could you upload your workbook (without sensitive data) somewhere (maybe via dropbox.com or google drive)?
    Then put the link here. Just the data from col B & K would be enough.
    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.

    Let me know in case you need any inputs.

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
  •