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
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