Delete ALL duplicates - VBA

[broch]

Hi All,

I have a dataset (columns A:L) on worksheet called "Total". Column A contains the row number of each record, and is the only unique field. I have another dataset on a worksheet called "Payables" which is a subset of the "Total" dataset, (ie: ALL the data in "Payables" appears in "Total").

What I need to do is identify all the records which are not payable. (ie: They would appear in the "Total" data but not in the "Payables" data). So I have created a third worksheet called "Non-Payables" and I've written code that copies and pastes both datasets onto this worksheet, with the "Payables" set being directly below the "Total" set.

The last step should be simple. I now need code that will check column A of the "Non-payables worksheet" identifying duplicates and deleting BOTH the rows on which the duplicates appear, thereby leaving me with a dataset which is essentially "Total" minus "Payables"

I've found loads of sites with code that will remove the second occurrences of each duplicate, but not BOTH duplicates. Can anyone help?

At the moment, I'm achieving this by using a recorded macro that highlights duplicates in red, then filters by colour, deletes all the red rows and clears the filter. This works, but boy is it slow and clunky.

Many thanks.

Brendan

 

[broch]

WOW!

Thanks GTO and Mirabeau.
The code samples you've both given are way beyond my translating ability.
But I'll give them both a try as soon as I can and report back.

Thanks a million for your help!

 

[Fazza]

Here is a non-code solution, using a query table. It is refreshable like a pivot table, though can be set to refresh automatically on file open or every n minutes. It can be made to do much more if required. It is a query on the other two worksheets - so, no populating the sheet with both other datasets & then modifying, just going directly to the result.

BTW, likely this could be run directly on your database without needing to populate Totals and Payables worksheets.

Assumes you have a worksheet name Totals and another Payables. I've assumed the common field name is "RecID". Start via menu ALT-D-D-N and follow the wizard, electing to edit in MS Query at the final step. (If you get a message about no visible tables select 'options' and then 'system tables' to see the worksheet names as data sources.) Edit the SQL to be like below.

regards

SELECT T.*
FROM {oj [Totals$] T LEFT OUTER JOIN [Payables$] P ON T.RecID = P.RecID}
WHERE P.RecID Is Null

 

[Fazza]

PS

In my previous post I assumed the data is well set up, with headers in row 1 and data immediately under. If not, modify the SQL to suit.

F