Consulting

Results 1 to 7 of 7

Thread: VBA - Clean up a table with returning values

  1. #1

    Lightbulb VBA - Clean up a table with returning values

    Hi all,

    It often occurs to me to clear a general ledger. What happens here is that positive numbers are booked in column A and negative numbers in column B. In the end I'm dealing with hundreds of rows that book out each other (i.e. 500 left and 500 right = (+500-500=) 0) and I'm looking for lines that are not canceled ( and therefore remain open). I was wondering if someone has a way that this can be filtered automatically very quickly so that when I open such a table I can immediately extract what is open.

    An example is made in the file below.

    Thanks a lot for any help!

    Best,
    Jovanni
    Attached Files Attached Files

  2. #2
    i made a function that will summarize your data.
    and then clear the content of column D and E.
    Attached Files Attached Files

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    I'm sorry but I wouldn't want you either as an Accountant or Bookkeeper if you were to apply this concept. Just because you have two matching financial figures doesn't justify why you can cancel them out. Surely you would need to ensure that they are related to the same client/transaction/account .....what ever you want to call it, before you can apply such a blanket rule.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Quote Originally Posted by Aussiebear View Post
    I'm sorry but I wouldn't want you either as an Accountant or Bookkeeper if you were to apply this concept. Just because you have two matching financial figures doesn't justify why you can cancel them out. Surely you would need to ensure that they are related to the same client/transaction/account .....what ever you want to call it, before you can apply such a blanket rule.
    Hi man, thanks for the input. I can see your point, I absolutely agree and I also wouldn't hire an accountant just filtering out an entire GL. However, you are taking the question for the function out of context here. For the record, I'm not an accountant, nor a bookkeeper. As a financial controller, I indeed do use accounting principles, and what you say is correct, but already controlled for.
    In order to obtain complete and accurate information, I have already incorporated these controls. First of all, I search by entity and I drill down on one account code (therefore controlling for two of your concerns). I am asking for a function that could potentially be used within a controlled environment. For me it is self-evident that I do not use it on a complete GL and empty everything, as it were. In principle, this is applicable for accounts that should be empty, so to speak, so for cleaning populate movement, accruals and bookings in the AR or AP made by accountants and managers. To address your other concerns, I already have a function that matches journals based on journal descriptions, references and/or journal IDs (in case of reversals). This way, the user is able to quickly find what he was looking for (when knowing what he was looking for).
    Didn’t thought it was necessary to provide this additional detail to receive a function like this, but eh, hope now it’s clear I don’t filter out an entity’s complete general ledger.

  5. #5
    Quote Originally Posted by arnelgp View Post
    i made a function that will summarize your data.
    and then clear the content of column D and E.
    The start of this is great, but I meant to see all openstanding bookings. In the example there was only one openstanding journal, but if there are >1 it just adds a total into the debit/credit. Whereas, I'd like to see these open individual journals (including description and all) below each other. That's why I was thinking about a filter.

    But thank you very much for the effort! Any chance you could take a look at this?
    Last edited by Jovannivk; 07-05-2022 at 02:43 AM.

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by Jovannivk View Post
    However, you are taking the question for the function out of context here.
    The info provided in Post #1 did not provide enough information to come to any other conclusion, however I'm not here to argue this.

    The following may give you some inspiration but it requires some extra work on your behalf. In cell F4 enter the formula "=If(D4<>"",Match(D4,$E$4:$E$10,0)+3" and fill down. In Cell G4 enter the formula "If(E4<>"",Match(E4,$D$4:$D$10,0)+3)" and fill down and in Cell H4 enter the following "If(Or(F4>0,E4>0),"Matched","") and fill down.

    By this stage you should see "Matched" for those balances that have an equal Credit and Balance

    The following code should then apply a filter based on the criteria "Matched" and hopefully delete those visible rows. Not tried here so only do this on a dummy sheet to determine the results
    Sub DeleteMatchedRows ()
    'Apply filter to a Range and delte visible rows
    Dim ws as Activesheet
    Dim trgRng as range
    Set trgRng = Activesheet.Range("B4:H10") <---- change to suit ledger area
    With trgRng
       .Autofilter, Field:= 8, Criteria:= "Matched"
       Application.DisplayAlerts = False
       .SpecialCells (xlCellsTypeVisible).Delete
       Application.DisplayAlerts = true
    End With
    ws.ShowAllData
    End Sub
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Quote Originally Posted by Aussiebear View Post
    The info provided in Post #1 did not provide enough information to come to any other conclusion, however I'm not here to argue this.

    The following may give you some inspiration but it requires some extra work on your behalf. In cell F4 enter the formula "=If(D4<>"",Match(D4,$E$4:$E$10,0)+3" and fill down. In Cell G4 enter the formula "If(E4<>"",Match(E4,$D$4:$D$10,0)+3)" and fill down and in Cell H4 enter the following "If(Or(F4>0,E4>0),"Matched","") and fill down.

    By this stage you should see "Matched" for those balances that have an equal Credit and Balance

    The following code should then apply a filter based on the criteria "Matched" and hopefully delete those visible rows. Not tried here so only do this on a dummy sheet to determine the results
    Sub DeleteMatchedRows ()
    'Apply filter to a Range and delte visible rows
    Dim ws as Activesheet
    Dim trgRng as range
    Set trgRng = Activesheet.Range("B4:H10") <---- change to suit ledger area
    With trgRng
       .Autofilter, Field:= 8, Criteria:= "Matched"
       Application.DisplayAlerts = False
       .SpecialCells (xlCellsTypeVisible).Delete
       Application.DisplayAlerts = true
    End With
    ws.ShowAllData
    End Sub
    This absolutely great man, this was exactly something I was thinking about. thanks for the work! I can make my corrections further from here. Again, thanks a lot!

    All the best.

Tags for this Thread

Posting Permissions

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