PDA

View Full Version : [SOLVED] Consolidate two reports based on multiple criteria



D_Marcel
09-25-2014, 11:22 AM
Hello experts, is everything alright? I hope so.
I came across a challenge that I thought that could be easily solved. At first, I thought that a code to eliminate duplicates with one or two criteria would be enough, but after a better understanding, I realized that I got a really complex scenario to handle in a single code.


I've read this amazing example posted here in the forum but unfortunately I don't know how to change it for my purpose:


http://www.vbaexpress.com/forum/showthread.php?10600-Solved-Delete-Duplicate-Rows-based-on-multiple-columns


I have a master database that need to be updated with the new registers daily. During the updating, I'll have the following scenarios, considering the second row as the register that came from the last update:

1. Delete duplicates based on the column "Invoice":



Business Place
Billing
Invoice
Status


4F01
100
100
Active


4F01
100

Active








In this case, I need to keep the row that contains a value in the field "Invoice" and eliminate the another one.

2. Delete duplicates



Business Place
Billing
Invoice
Status


4F01
100

Active


4F01
100

Active








If the field "Invoice" is null to both registers, eliminate any.

From this point, the complexity becomes high:

3. The register was canceled:



Business Place
Billing
Invoice
Status


4F01
100
100
Active


4F01
100

Canceled








If the field "Invoice" is not null, the status of the this row should be "Cancel" and the second row, eliminated:



Business Place
Billing
Invoce
Status


4F01
100
100
Cancel






4. Delete both



Business Place
Billing
Invoice
Status


4F01
100

Active


4F01
100

Canceled








If the "Invoice" is null and the register was canceled, eliminate both.

As I need this routine to be automatic, I'll will continue the development using the .find method in the master database and handle the registers, case by case, although that will demand much more time.

Can anyone give me some ideas? I would be deeply grateful.

Best regards,

Douglas Marcel de Moraes