Duplicate Query with a twist...
 

Hi,

I can identify duplicates based on the ID and name as outlined below,
However what I need to be able to do is to identify duplicates and
delete or tag the row containing the lowest Createdate. This is further
compounded by the fact that if a record has the same id, name and
createdate then the Outcome field should filter on using the outcome
field where the outcome description Sold overides any other. ( See last
two rows in exapnle data)

ID Name Createdate Outcome
8,431 MOORCROFT RECOVERY Ltd 09/11/2006 Done
24,570 Air Affairs (SA) Ltd 13/11/2006 Sold
25,698 Pickfords Business Moving 01/11/2006 Sold
35,416 Personal Performance Cons 01/11/2006 Done
42,270 National School of Government 09/11/2006 Sold
43,838 National School of Government 09/11/2006 Done
45,299 Fiesta Blinds & Fabrics 13/11/2006 Done
104,635 James Stack & Sons Ltd 14/11/2006 Done
114,084 Anders Elite 12/10/2006 Sold
120,666 Remsdaq Ltd 30/10/2006 Sold
120,723 BANNER BUSINESS 09/11/2006 Undecided
120,723 BANNER BUSINESS 09/11/2006 Sold


Thanks for your help :)

B

Duplicate Query with a twist...
 

This can be done quite quickly with worksheet formula and sorting...

1) Create a new column which combines ID and Name (lets call it ID_Name),
using this formula (assuming first data cell is A2):
= A2 & "_" & B2
2) Sort the table by ID_Name (asc), CreateDate (desc), Outcome (create a
custom list that gives the priority order you want)
3) Add another column labelled Tagged, using this formula (starting in row 2)
=AND(E2=E1,E2<E3)
(assuming the ID_Name column is E)
4) Copy columns ID_Name and Tagged, then PasteSpecial-Values to remove the
formula and leave only the result.
5) Sort data by the Tagged column
6) Delete all records where Tagged = True

Of course, all this could be automated in VBA, but that would probably take
longer than the manual approach...

Cheers,
Dave

"Bryan" wrote:

Hi,

I can identify duplicates based on the ID and name as outlined below,
However what I need to be able to do is to identify duplicates and
delete or tag the row containing the lowest Createdate. This is further
compounded by the fact that if a record has the same id, name and
createdate then the Outcome field should filter on using the outcome
field where the outcome description Sold overides any other. ( See last
two rows in exapnle data)

ID Name Createdate Outcome
8,431 MOORCROFT RECOVERY Ltd 09/11/2006 Done
24,570 Air Affairs (SA) Ltd 13/11/2006 Sold
25,698 Pickfords Business Moving 01/11/2006 Sold
35,416 Personal Performance Cons 01/11/2006 Done
42,270 National School of Government 09/11/2006 Sold
43,838 National School of Government 09/11/2006 Done
45,299 Fiesta Blinds & Fabrics 13/11/2006 Done
104,635 James Stack & Sons Ltd 14/11/2006 Done
114,084 Anders Elite 12/10/2006 Sold
120,666 Remsdaq Ltd 30/10/2006 Sold
120,723 BANNER BUSINESS 09/11/2006 Undecided
120,723 BANNER BUSINESS 09/11/2006 Sold


Thanks for your help :)

B