VBA - Clean up a table with returning values

[Jovannivk]

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

[arnelgp]

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

[Aussiebear]

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.

[Jovannivk]

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.

[Jovannivk]

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?

[Aussiebear]

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

[Jovannivk]

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.