How to delete both sets of duplicate date?

[gema]

Hello.

I currently have about 3000 rows of data, much of it being duplicate data. Is there a way to have it search for duplicate data, and have it delete BOTH sets of duplicate data?

For example, say this is what is in my spreadsheet:

Smith John 1234 Main St. Anytown, IN
Smith John 1234 Main St. Anytown, IN
Bobbert Bob 5123 Red. St. Thistown, IN
Nugent Ted 1211 My St. Thattown, IN
Nugent Ted 1211 My St. Thattown, IN
Jordan Michael 23 All-Star Ln. Town, IN

I would want it to delete both John Smith records and both Ted Nugent records, leaving Bob Bobbert's and Michael Jordan's records.

Thanks in advance!

[ChemistB]

Will this work for you; If your data is in A1:A6, in an empty column type this equation and drag down

Please Login or Register  to view this content.

Then autofilter on that column for "Delete" and delete all visible rows.

[gema]

With that formula, it is deleting all instances of anyone with the same last name. So if there are two "Mike Smith" entries and one "Bob Smith", it is deleting all three of those, where I only want it to delete the "Mike Smith" entries.

Do you see any way around this?

[Cheeky Charlie]

Easiest (ugly but quick) way:
put this in H1
=A1&B1&C1&D1&E1&F1&G1
put this in I1
=IF(COUNTIF($H$1:$H$6,H1)>1,"Delete","") (redefine range as apt)
copy down

autofilter and delete "Delete" rows

HTH

[gema]

That worked. Thanks!