Removing duplicate rows

  • Thread starter Thread starter MARTY
  • Start date Start date
M

MARTY

Hello:

I was wondering if anyone knows if Excel has the built-in
capability of removing duplicate rows from a spreadsheet.

Example: I have 100 rows in a worksheet, but only 75 of
them are unique. Is there a pre-programmed function or
command I can use to delete the 25 duplicates?

I know it can be VBA'ed failrly easily, but I don't want
to spend the time if I can do it with a few mouse clicks.

Anyone? Thanks in advance.
MARTY
 
Aaron:

There might be a way to do this, but I doubt it. Sounds
like this would be prohibited in order to avoid an
ID4 "infect the mother ship with a virus so that it will
spread to all of the children" scenario. I don't believe
I would do this (personally) even if it could be done.

What CAN be done is to ensure that all of your global
users get a copy of the spreadsheet which contains a
macro. When users open their local copies, the macro
looks at the revision date of the shared version, and
then (if the shared version is "newer") automatically (or
with a "do you wish to update?" message box) updates the
copy on the global computers. In other words, the user
initiates the update, not the other way around.

Now, this could still theoretically spread a virus, but
it wouldn't be done en masse and would be easier to
eradicate because not all copies would be infected
simultaneously.

Just some food for thought.

MARTY
 
You can use the Advanced Filter to select unique records.

To use this, select a cell somewhere within your list; go to
Data/Filter/Advanced filter. Select "copy to a new location", adjust the List
range if necessary, Leave the Criteria box blank, fill in the Copy to address,
and check the box for Unique records.

Caveat: let's say your data consists of 4 columns per record. Excel considers
two rows to be duplicates only if ALL 4 columns are identical. If you want to
consider them to be duplicates if only 1 column, say A, matches, then you
should adjust the List range to include only that column, and say to Filter in
Place. Then you'll have to manually copy the visible records to a new
location.
 
Did you post this in the wrong thread?

Aaron:

There might be a way to do this, but I doubt it. Sounds
like this would be prohibited in order to avoid an
ID4 "infect the mother ship with a virus so that it will
spread to all of the children" scenario. I don't believe
I would do this (personally) even if it could be done.

What CAN be done is to ensure that all of your global
users get a copy of the spreadsheet which contains a
macro. When users open their local copies, the macro
looks at the revision date of the shared version, and
then (if the shared version is "newer") automatically (or
with a "do you wish to update?" message box) updates the
copy on the global computers. In other words, the user
initiates the update, not the other way around.

Now, this could still theoretically spread a virus, but
it wouldn't be done en masse and would be easier to
eradicate because not all copies would be infected
simultaneously.

Just some food for thought.

MARTY
Click to expand...
 
Hi Marty,

Go to "Data", "Filter", then "Advanced Filter". Select
your data range and then check the box to return only
unique records. Be sure that you have a header on your
column.
 
Back
Top