Delete rows with duplicate data
Posted by Brent Mosley on October 10, 2001 10:44 AM
Has anyone done a macro that will retain the compare values in a column and will retain the first of each unigue entry and delete the rows with repetitive data?
Example:
Name Address SS Number
tom 111 X Drive 111-11-1111
tom 111 X Drive 111-11-1111
tom 111 X Drive 121-11-1111
In the above example the second line would be delted based on unique data in th SS Number column
Posted by IML on October 10, 2001 10:57 AM
non-macro way
Brent,
If you don't require a marco, you could highlight your social security row and goto data, filter, advance filter. Check unique records only and okay.
Highlight the collapsed list. Hit control G, special, visable cells only and control c to copy. Then control V to paste your list on a new sheet with no duplicates.
I'm not all that savvy on filters, so there may be a better way.
good luck
Posted by Eric on October 10, 2001 11:09 AM
Also...
If you tell the advanced filter to put the results in a different column (rather than filtering the list in place) you can skip the copy paste as values steps.
HTH
Posted by Loren on October 10, 2001 11:49 AM
duplicate data-delete dupe in 1st column?
The advanced filter deletes rows if the entire record is a dupe.
How about if dupes are based on a single column? thanks
Posted by IML on October 10, 2001 11:58 AM
Re: duplicate data-delete dupe in 1st column?
Using the method I suggested, it based only on the single column. That is the difference between my longer way and the shorter way Eric suggested. Both of which can be valid depending on what you are trying to acheive...
Posted by Eric on October 11, 2001 6:16 AM
I'm confused :?
My recommendation was to use Data|Filter|Advanced Filter, then check "unique records only" and click on the "Copy to another location" radio button. In my hands the "copy to another location" option does NOT alter entire rows. Am I missing something?
Posted by IML on October 11, 2001 6:27 AM
Re: I'm confused :?
Eric,
If I'm reading this right he is asking how to only show rows 1 and 3 in this example by keying on, for example name only
Name address
john doe 123 anywhere
john doe 234 anywhere
jane day 456 anywhere else
There is probably a better way to do it, but that why I suggested only filter that column and then selecting the whole list and copying visable cells. That's just my interpretation of it ...
Posted by Eric on October 11, 2001 6:34 AM
Whoa, I need to lay off the cough syrup :-) Thanks Mr. IML!
: My recommendation was to use Data|Filter|Advanced Filter, then check "unique records only" and click on the "Copy to another location" radio button. In my hands the "copy to another location" option does NOT alter entire rows. Am I missing something?
