Deleting Duplicate rows?
Posted by Rick M on May 31, 2001 5:36 AM
I know this has been addresses here, but I cannot find it.
I would like to delete duplicate rows of data.
My sheets has 14259 rows od data 5 columns wide.
There are many duplicate rows, how can I aoutomatically delete the duplicate and leave one unique entry?
Thanks.
Posted by Aladin Akyurek on May 31, 2001 5:56 AM
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
Posted by Rick M on May 31, 2001 6:16 AM
Thanks Aladin - I'll try that.
The data is coming from an Access Database that has dupilcate records.
Posted by Mark W. on May 31, 2001 8:44 AM
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.
Posted by Sandor on May 31, 2001 4:17 PM
The formula in step 2 should read :-
=COUNTIF($C$2:C2,C2)
} 1. Enter the formula, =A2&","&B2, into cell C2 and
Posted by Mark W. on June 01, 2001 7:08 AM
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
Posted by Ferenc on June 02, 2001 6:16 AM
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 }
