Reconciliation

Hi Folks,

I need some expert advise in how to develope a macro function to reconcile some lines that correspond to some criteria.

The example attached will give you some idea of what I am talking about. I need to find a simple and quick way to remove from this table the lines where "Supplier" AND "Invoice" values are the same AND the sum of "Amount" is equal to zero.

Filtering it is laso a solution. But it's not very practical when you have a list of 1,000 different suppliers.

Much appreciate any advise.

Hi
Your workbook does not show any same supplier & invoice except probably A & 123-A. how to handle negative amount.
Ravi

Originally Posted by ravishankar

Hi
Your workbook does not show any same supplier & invoice except probably A & 123-A. how to handle negative amount.
Ravi

Hi Ravi,

Sorry, maybe I was not very explicit in what should be the criteria for the lines to be removed.

For instance you have supplier A mentioned in 3 different records: lines 2, 11, 12. But only line 2 and 12 have the same invoice number. And therefore they can be removed since the sum of Amount column for these 2 lines is zero.

I’ve amended the table to indicate the lines that should be picked up by the macro and removed as they satisfy the criteria.

Hope this makes it more clear now.

Regards,
Eduard

Anyone able to help me with this?

I realize I might need to use LOOKUP or VLOOKUP functions but not quite sure how to do that.

Thanks in advance for any feedback.

Eduard

Hi,

Just in case someone is interested I've found a kind of solution.

Please Login or Register  to view this content.

What this macro does is the following: creates 2 additional columns D and E. In column D I'm adding the invoice number to supplier name. In column E I'm entering SUMIF formula to add up the amounts in column C for which the supplier+invoice number is the same. Than it filters for the lines where this sum is zero. And deletes those lines. At the end it just displays a message that macro has been completed.

I accept, this does not look very professional. But it is at least something. If anyone can help me improve it, will be much appreciated.

Namely:
1. I'm not very happy that after filtering for zero values macro selects the filtered records starting with E2. Is there a way to indicate that the selection should start from the first record with zero value? For instance in this particular case the first zero value was in E2 cell. What if that would be E4 in another case?
2. How do I extend the formulas entered in D2 and E2 to the last row of the table?
3. How do I include an information about the number of deleted records into the message box?

Thanks in advance for any feedback.

Eduard

Hi
I have modified the macro to delete rows if the sum is zero. I notice a problem. C 12-X has 3 rows which gives zero on totalling. I get to delete only the last row. Try it. I will change codes based on your reply
RAvi

Hi Ravi,

It's a good start.

What I would do first is remove that message box that displays the value of E1 cell. It's not very handy to press Ok button hundred times when you have a large table with 1,000 records. Let the macro run until the very end without users interference into the process.

Second, the final result is not exactly what I'm trying to achiev. Like for instance in case of supplier C all 3 lines (4, 10, 13) have to be deleted as the invoice number is the same (12-X) for all of them and the sum of these lines is zero.

I've attached another spreasheet which shows how the final table has to look like.

Thanks and regards,
Eduard