Macro to delete rows with offsetting values - Accounting

[BK]

Hi Everyone,

I've got a large workbook, approximately 700k entries, where I need to find entries that don't have a canceling values. Here's an example:

5,000
5,000
-5,000
-5,000
1,000
-1,000
1,000

In this case, one of the 1,000's would be left and the other rows deleted. There's also extra information in other columns if that makes a difference. The fact that one of the 1,000's is the wrong one to delete doesn't bother me as I'll at least know the dollar amount of a problem entry.

I found a reference to this idea here:
http://www.excel-answers.com/microsoft/Excel-Programming/30753434/move-offsetting-values.aspx

Unfortunately the macro wasn't working for me and I was unsuccessful at debuging it.

Thanks all for your help!

BK

 

[pascal baro]

I can see with vba if you really want vba code to do this.

the other way to do this from quick first thought would be to turn every numbers into positive, use countif on

It can be done using countif

 

[pascal baro]

Hi BK

I can see with vba if you really want vba code to do this.

the other way to do this from quick first thought would be to turn every numbers into positive, copy the original list to an advanced filter unique value list, apply countif on the unique value over the original list and using a condition if that number is even, it cancels, else one value remains.

Now, if you have other value on the same line you want to keep for reference purpose, vba should perform better

Pascal Baro

 

[BK]

The way you mention doesn't really work out well with the data set I'm working with. When I have, say, 500 entries of $1,000 and -$1,000, using ABS and filtering uniques isn't helpful as it could be that in total the $1,000 grouping of entries don't offset each other and I have 1,000 or -1,000 left over. If it didn't balance, I could then go through that grouping and find the rogue entry.

Again, I'm working with 700,000 entries and I'm pretty sure VBA is the way to do this. I know we have values that aren't offsetting each other but OneSAP is unhelpful in this regard other than showing that things aren't balancing.

Thanks!

BK

 

[thompson759]

Hi Everyone,



I've got a large workbook, approximately 700k entries, where I need to find entries that don't have a canceling values. Here's an example:



5,000

5,000

-5,000

-5,000

1,000

-1,000

1,000



In this case, one of the 1,000's would be left and the other rows deleted.. There's also extra information in other columns if that makes a difference. The fact that one of the 1,000's is the wrong one to delete doesn't bother me as I'll at least know the dollar amount of a problem entry.



I found a reference to this idea here:

http://www.excel-answers.com/microsoft/Excel-Programming/30753434/move-offsetting-values.aspx



Unfortunately the macro wasn't working for me and I was unsuccessful at debuging it.



Thanks all for your help!



BK

I have not reconciled accounts from SAP but I have with JDE and VBA is the only way to do it. I compare one document type and amount against the offsetting document type and amount.

The basic process is to get the first unreconciled document type and amount, then look through the list of offsetting doucment types and amounts, whena match is found, both are marked as reconciled in an offsetting column.

When the process is complete you are left with the unreconsiled items.

I do not have any code to give you as I do not know the format of your data.. In addition, my reconciling process are more encompasing that a simple snippet of code as several different methods are used to reconsile.
M