delete all dulplicate rows leaving no record of the duplicates
 

I am using excel 2002. I have a row of data, with some duplicated records. I
want to be able to delete ALL duplicates.

e.g.
bob
bob
scott
john

would delete all record of bob leaving just the following:

scott
john

How can I achieve this? Any help will be greatly apprenciated.

Many thanks

--
nozzaworld

delete all dulplicate rows leaving no record of the duplicates
 

One way to do it would be to add a 'helper' column. Assuming the names are
in column A on the sheet and begin in row 2, put this formula into an
available cell in row 2:
=COUNTIF(A:A,A2)
Fill the formula down to the end of your names list. Use Data -- Filter
and choose 1 to leave only the truly unique entries displayed. Copy all that
is displayed and paste into another sheet. Remove the data filter on the
original sheet and delete ALL rows of information, go back to the other sheet
and copy the list back into the original sheet. For cleanup, also delete the
helper column.


"nozzaworld" wrote:

I am using excel 2002. I have a row of data, with some duplicated records. I
want to be able to delete ALL duplicates.

e.g.
bob
bob
scott
john

would delete all record of bob leaving just the following:

scott
john

How can I achieve this? Any help will be greatly apprenciated.

Many thanks

--
nozzaworld

delete all dulplicate rows leaving no record of the duplicates
 

Thank you for your help.

However I have found that my duplicated fields have spaces at the end of
them so they are not showing up as being duplicates.

I wonder whether you know how I am able to remove the spaces from my data?

e.g. so that the two fields below both read nozzaworld
nozzaworld
nozza world

Many thanks
--
nozzaworld


"JLatham" wrote:

One way to do it would be to add a 'helper' column. Assuming the names are
in column A on the sheet and begin in row 2, put this formula into an
available cell in row 2:
=COUNTIF(A:A,A2)
Fill the formula down to the end of your names list. Use Data -- Filter
and choose 1 to leave only the truly unique entries displayed. Copy all that
is displayed and paste into another sheet. Remove the data filter on the
original sheet and delete ALL rows of information, go back to the other sheet
and copy the list back into the original sheet. For cleanup, also delete the
helper column.


"nozzaworld" wrote:

I am using excel 2002. I have a row of data, with some duplicated records. I
want to be able to delete ALL duplicates.

e.g.
bob
bob
scott
john

would delete all record of bob leaving just the following:

scott
john

How can I achieve this? Any help will be greatly apprenciated.

Many thanks

--
nozzaworld

delete all dulplicate rows leaving no record of the duplicates
 

With another helper column!

in an available column, at the top of the list (again, assuming names in A)
=TRIM(A2)
now you should be able to either:
1) do the =COUNTIF() but referencing this new column instead of A, OR
2) copy the data in the new column and the use Edit -- Paste Special with
the "values" option selected to paste it over the original entries in column
A and proceed as before. You can then go back and delete the =TRIM()
formulas column.


"nozzaworld" wrote:

Thank you for your help.

However I have found that my duplicated fields have spaces at the end of
them so they are not showing up as being duplicates.

I wonder whether you know how I am able to remove the spaces from my data?

e.g. so that the two fields below both read nozzaworld
nozzaworld
nozza world

Many thanks
--
nozzaworld


"JLatham" wrote:

One way to do it would be to add a 'helper' column. Assuming the names are
in column A on the sheet and begin in row 2, put this formula into an
available cell in row 2:
=COUNTIF(A:A,A2)
Fill the formula down to the end of your names list. Use Data -- Filter
and choose 1 to leave only the truly unique entries displayed. Copy all that
is displayed and paste into another sheet. Remove the data filter on the
original sheet and delete ALL rows of information, go back to the other sheet
and copy the list back into the original sheet. For cleanup, also delete the
helper column.


"nozzaworld" wrote:

I am using excel 2002. I have a row of data, with some duplicated records. I
want to be able to delete ALL duplicates.

e.g.
bob
bob
scott
john

would delete all record of bob leaving just the following:

scott
john

How can I achieve this? Any help will be greatly apprenciated.

Many thanks

--
nozzaworld