Hi Rick,
Try asap-utilities which is a free download at www.asap-utilities.com.
Forgive my ignorance, but how do you "manage" to get those dup recs?
Aladin
Thanks Aladin - I'll try that.
The data is coming from an Access Database that has dupilcate records.
Rick, unless I'm mistaken ASAP-Utilities will
only eliminate duplicates in a single column.
If your data set has multiple columns consider
the following...
Let's assume that cells A1:B6 contain...
{"Field1","Field2"
;"Cat","Mouse"
;"Dog","Bone"
;"Cat","Mouse"
;"Mouse","Cheese"
;"Mouse","Cheese"}
1. Enter the formula, =A2&","&B2, into cell C2 and
Copy down. Note: It's a good idea to delimit
variable length values with a character (comma in
this case) not found in your data set.
2. Enter the formula, =COUNTIF(C2:$C$6,C2), into
cell D2 and Copy down.
3. AutoFilter column D:D for value(s) that "does
not equal" 1
4. Delete all visible data rows.
The formula in step 2 should read :-
=COUNTIF($C$2:C2,C2)
} 1. Enter the formula, =A2&","&B2, into cell C2 and
It makes no difference...
Sandor, =COUNTIF($C$2:C2,C2), identifies the 1st
occurance of a record with a 1 while =COUNTIF(C2:$C$6,C2)
identifies the last occurance. Either way, the
results after record deletion are the same. The formula in step 2 should read :- } : 1. Enter the formula, =A2&","&B2, into cell C2 and
Oh? Well.....yes and no.......
The formula '=COUNTIF(C2:$C$6,C2)' will only produce correct results if copied down no further than cell C6. If rows are added below row 6, this formula would need to be changed before it can be copied down past row 6. (I.E. $C$6 would have to be changed to the last cell ref with data in column C)
The formula '=COUNTIF($C$2:C2,C2)' can be copied down as far as required without having to be changed.
For what it's worth, I would therefore recommend the second formula.
Sandor, =COUNTIF($C$2:C2,C2), identifies the 1st }