How to remove duplicated data repeated....

[Kimmie]

I am working on a spreadsheet which has many different fields. The first
column is Last Name and the second column is first name. My objective is to
delete the rows with duplicate names and only keep one of the rows. I tried
using the following formula but it was only reading the first column which is
last name. How do I change it to read the first and second column.

For instance,

Smith John
Smith John
Shot, Steve
(Keep only one John Smith and delete the other.)

My Second project which is a little different, is to take this completed
file (the one that only has John Smith and Steve Shot left and remove the
duplicates) and compare with another spreadsheet. I was going to add the
spreadsheet fields to this final spreadsheet and then I will need to remove
all duplicates. What formula do I need to apply so that I can use the filter?

Then, I have two projects....one I need to delete the "duplicate" rows and
the other I need to

 

[Ashish Mathur]

Hi,

In cell C1, enter the following formula =A1&B1 and copy down. In cell D1,
use the following formula if(countif($A$1:A1,A1)>1,"a",1). Now select
column D and navigate to Edit > Go To > Special. Click on the Formulas
radio button and check the box for Text (Please remember to keep the other
boxes unchecked). Now all the "a"'s in column D will be highlighted. You
can simply delete all the rows now.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Kimmie]

Just learned something new with two of the formulas that you provided....now
how do I delete all rows that are duplicate?

Smith, John
Smith, John
Smith, John
Smith, Robert
Smith, Smith
(Delete all Johns using a similiar spreadsheet layout that you provided with
putting last name and first name together.)

I cannot thank you enough...I just learned about this helpful site. Total
lifesaver!

 

[Ashish Mathur]

Appreciate your feedback

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Kimmie]

I did not see a response to this question yet.

Just learned something new with two of the formulas that you provided....now
how do I delete all rows that are duplicate?

Smith, John
Smith, John
Smith, John
Smith, Robert
Smith, Smith
(Delete all Johns using a similiar spreadsheet layout that you provided with
putting last name and first name together.)

I cannot thank you enough...I just learned about this helpful site. Total
lifesaver!

 

[Peo Sjoblom]

Use data>filter>advanced filter, select unique records, then copy to another
location
That will give you a list without all the duplicates

--


Regards,


Peo Sjoblom

 

[Kimmie]

If I have the following listed, how do I remove all of the John Smiths? I
want any field showing up 2 or more times removed completely.

Smith, John
Smith, John
Smith, John
Doe, Linda
Schmidt, Steve
Moore, Dave
Moore, Dave

I would like the final report only to show Linda and Steve but remove all
John Smiths and all Dave Moore because they are appearing more than once.

 

[Peo Sjoblom]

Assume you have a header in A4 and your names start in A5
in row 2 in F2 put


=COUNTIF($A$5:$A$500,A5)=1

adapt the range to fit and replace all cell references with your actual ones

Select the table including the headers

do data>filter>advanced filter, select copy to another location, preferably
above or below the table itself. In the criteria box use


$F$1:$F$2


once again use the actual references you have in your workbook, leave F1
blank
and then click OK

which will give you

Names
Doe, Linda
Schmidt, Steve

where Names is the fictive header I used





--


Regards,


Peo Sjoblom