Page 1 of 9 123 ... LastLast
Results 1 to 10 of 83

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

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

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

    Hi Experts,

    I have a sheet which runs into over 30000 rows. I need to find contra entries within a company in column B for a each job in column C. Basically I need to find which transactions sum to 0 in column K (Balance in Base). so for example, in company code IN9073 in column B, there are 30 transactions which sum to 3,000 in column K (Balance in base). I have manually identified whether in each transaction is contra on not in column L (Marker2). If a particular transaction is not summing to 0, I have marked it as WIP.

    The problem is there can be transaction with amount + and - which can be identified AND transactions where different amounts with different signs sum to 0.

    I need this to be achieved by way of some formula or VBA. I tried sumifs but failed when there are 10 transactions out of which 8 are contra and 2 are not.

    The HTML of my excel is below,

    P.s. Rounding off to 1 or 2 is completely allowed

    Thanks in advance.



    ABCDEFGHIJKL
    1Company No.Company CodeJob No.Account No.Trans. No.Journal No.Date PostedEntry DateDebitsCredits Balance in Base Manual
    58110IN9073110695711600511012100111014223830/11/201030/11/20101,000.000.00 1,000 CONTRA
    59110IN9073110695711600511012100111014223830/11/201030/11/20102,000.000.00 2,000 CONTRA
    60110IN9073110695711600511015472411013261731/03/201029/03/20100.0038,500.00 -38,500 CONTRA
    61110IN9073110695711600511076457711013045308/02/201008/02/20101,000.000.00 1,000 CONTRA
    62110IN9073110695711600511076458511013046208/02/201008/02/20101,000.000.00 1,000 CONTRA
    63110IN9073110695711600511076461611013049408/02/201008/02/20101,000.000.00 1,000 CONTRA
    64110IN9073110695711600511076464011013052408/02/201008/02/20101,000.000.00 1,000 CONTRA
    65110IN9073110695711600511076468511013057809/02/201009/02/20101,000.000.00 1,000 CONTRA
    66110IN9073110695711600511076472011013062710/02/201010/02/20101,000.000.00 1,000 CONTRA
    67110IN9073110695711600511076490511013104322/02/201022/02/20101,000.000.00 1,000 CONTRA
    68110IN9073110695711600511076491011013105722/02/201022/02/201012,500.000.00 12,500 CONTRA
    69110IN9073110695711600511076511911013157805/03/201005/03/20101,000.000.00 1,000 CONTRA
    70110IN9073110695711600511076523211013179413/03/201013/03/20101,000.000.00 1,000 CONTRA
    71110IN9073110695711600511076528811013185013/03/201013/03/20102,000.000.00 2,000 CONTRA
    72110IN9073110695711600511076529211013185413/03/201013/03/20103,000.000.00 3,000 CONTRA
    73110IN9073110695711600511076529411013185613/03/201013/03/20101,000.000.00 1,000 CONTRA
    74110IN9073110695711600511076530911013187113/03/201013/03/20101,000.000.00 1,000 CONTRA
    75110IN9073110695711600511076532011013188213/03/201013/03/20101,000.000.00 1,000 CONTRA
    76110IN9073110695711600511076534011013190213/03/201013/03/20101,000.000.00 1,000 CONTRA
    77110IN9073110695711600511076535511013191715/03/201015/03/20101,000.000.00 1,000 CONTRA
    78110IN9073110695711600511076539111013200516/03/201016/03/20101,000.000.00 1,000 CONTRA
    79110IN9073110695711600511076554311013229023/03/201023/03/20102,000.000.00 2,000 CONTRA
    80110IN9073110695711600511076554411013229123/03/201023/03/20102,000.000.00 2,000 CONTRA
    81110IN9073110695711600511076554611013229323/03/201023/03/20102,000.000.00 2,000 CONTRA
    82110IN9073110695711600511076640111013445121/05/201021/05/20100.001,000.00 -1,000 CONTRA
    83110IN9073110695711600511076640211013445221/05/201021/05/20100.001,000.00 -1,000 CONTRA
    84110IN9073110695711600511076640311013445421/05/201021/05/20100.001,000.00 -1,000 CONTRA
    85110IN9073110695711603011071968411014101530/10/201028/10/20101,000.000.00 1,000 WIP
    86110IN9073110695711603011071968611014101530/10/201028/10/20101,000.000.00 1,000 WIP
    87110IN9073110695711603011071968811014101530/10/201028/10/20101,000.000.00 1,000 WIP

    Sheet1



    Last edited by Miratshah; Feb 11th, 2019 at 02:43 AM.

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

    How do you know that the last 3 items are WIP ?
    Why should it be those 3 items and not some other items ?

    It looks like maybe you can do it simply by analysis of the ACCOUNT No., column D.
    All items with 116005 sum to zero, and the other three with 116030 do not sum to zero.
    Is it as simple as that ?
    In which case some formula using SUMIF or SUMIFS may be helpful.
    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

  3. #3
    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

    Hi Gerald,

    Thanks for reverting. Yes, in this case the unique identifier is Account number. However in many cases, that is not the case. From 30 transactions for example, 25 will sum to 0 where other 5 wont. Unfortunatey SUMIFs is not very useful in this case.

    Is there other formula or VBA we can use? Please note, in many cases, amounts are different. Not just + compensating with -.

    Thanks in advance.

  4. #4
    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

    OK, so if we can't use Account Number, how DO we identify the WIP items ?
    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

  5. #5
    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 Gerald Higgins View Post
    OK, so if we can't use Account Number, how DO we identify the WIP items ?
    Hi Gerald,

    It might sound illogical, but is there a way to identify those transactions sum to 0?

    Just to give more perspective, in below case, two different account numbers are used, however all transaction for 1 job sum to 0, hence all are marked as contra in column L.



    ABCDEFGHIJKL
    1#9BC2E6 ;;">Company No.#9BC2E6 ;;">Company Code#9BC2E6 ;;">Job No.#9BC2E6 ;;">Account No.#9BC2E6 ;;">Trans. No.#9BC2E6 ;;">Journal No.#9BC2E6 ;;">Date Posted#9BC2E6 ;;">Entry Date#9BC2E6 ;;">Debits#9BC2E6 ;;">Credits#9BC2E6 ;;"> Balance in Base #9BC2E6 ;;">Manual
    90110IN9073110422911600511071737311012632125/09/200925/09/20090.001,537.00 -1,537 CONTRA
    91110IN9073110422911600511071737311012632125/09/200925/09/20090.001,500.00 -1,500 CONTRA
    92110IN9073110422911600511075913711011687013/01/200913/01/20091,537.000.00 1,537 CONTRA
    93110IN9073110422911600511075935211011753404/02/200904/02/20091,500.000.00 1,500 CONTRA
    769110IN9073110422911600511012116011020083010/12/201510/12/20150.001,537.00 -1,537 CONTRA
    770110IN9073110422911600511012116011020083010/12/201510/12/20150.001,500.00 -1,500 CONTRA
    771110IN9073110422911603011073133911020122429/12/201528/12/20153,037.000.00 3,037 CONTRA

    Sheet1




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

    Forget about Excel for a moment, you need to know how to do this without Excel.

    Give us some sample data showing items that CAN be identified as CONTRA and ALSO some that are NOT CONTRA.

    Then explain how we identify them.

    Once you have explained a method for doing this outside Excel, then we might be able to come up with a way of replicating that within Excel.
    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

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

    Hi Gerald,

    Okay got the point you are trying to make.

    So I manually do following steps:-

    1. Select 1 company code from column B.
    2. Select 1 Job number from column C.
    3. Check if the subtotal in column K (balance in base) is 0. If yes, mark contra against every transaction. A pivot by job number & company code helps achieve this faster.
    4. Now its time to check each and every job by company code where subtotal is not 0. I manually filter each job and try to identify if few of the transactions sum to 0 in column K or not. If out of say for example 50 transaction in 1 job, 40 sum to 0, I mark them contra and others as WIP. Sometimes if amounts are not identical, account number filter helps by narrowing the criteria. This does result into mistakes since manually identifying transactions that sum to 0 for so many transactions is a tiresome task.

    Let me know if I can help you more on this.

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

    OK, we can probably replicate steps 1-3 in Excel fairly easily.

    But I don't know how to do step 4 in Excel, at least I don't know how to do it consistently and accurately.
    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

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

    Hi Gerald,

    Yes, That is where I am stuck as well. It consumes 2 man days at the moment to do it manually. Can we make use of "Solver" to do this? Just thinking aloud.

  10. #10
    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

    Hi Gerald,

    alternatively was wondering, if a code similar to this can be used ?

    Code:
    FunctionGetCombination(CoinsRange AsRange, SumCellId AsDouble) AsString    Dim xStr As String
        Dim xSum As Double
        Dim xCell As Range
        xSum = SumCellId
        For Each xCell In CoinsRange
            If Not (xSum / xCell < 1) Then
                xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
                xSum = xSum - (Int(xSum / xCell)) * xCell
            End If
        Next
        GetCombination = xStr
    EndFunction

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
  •