Finding and deleting both duplicate rows

I deal with leads for a sales room and get sent over leads in bulk, I've created a master scrub list that I can attach to the end of a new lead file and sort by number to show which are duplicates.

When you do the: Data, Filter, Advanced Filter, select Unique Records, it hides the duplicate but what I need is not only for the duplicate to be hidden or gone but the row that it is a duplicate of, i.e. I need BOTH rows to go

Name-----number
Dave 555-1212
Dave 555-1212
John 536-2343
Smith 423-2312


needs to become

Name-----number
John 536-2343
Smith 423-2312

I would need a formula that figured out that Dave with number 555-1212 was a duplicate and delete BOTH rows, can anyone help?

ROFL... sorry Dave =p

Yes, I need both records deleted...

Any ideas?

Hi

How about

Sub aaa()
  For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) = Cells(i - 1, 2) Then
      Cells(i, 1).EntireRow.Delete
      Cells(i - 1, 1).EntireRow.Delete
    End If
  Next i
End Sub

rylo

Where would I add this code to make it work... I'm kinda new at excel and the guy that normal handles this is gone for a week

Originally Posted by udfxrookie

I deal with leads for a sales room and get sent over leads in bulk, I've created a master scrub list that I can attach to the end of a new lead file and sort by number to show which are duplicates.

When you do the: Data, Filter, Advanced Filter, select Unique Records, it hides the duplicate but what I need is not only for the duplicate to be hidden or gone but the row that it is a duplicate of, i.e. I need BOTH rows to go

Name-----number
Dave 555-1212
Dave 555-1212
John 536-2343
Smith 423-2312


needs to become

Name-----number
John 536-2343
Smith 423-2312

I would need a formula that figured out that Dave with number 555-1212 was a duplicate and delete BOTH rows, can anyone help?

Try this:
colA: holds names
colB: holds phone numbers

create a helper column C
in C2: =SUMPRODUCT(--($A$2:$A$10=A2),--($B$2:$B$10=B2))
copy down as far as needed

Auto Filter > custom > is greater than 1
select row numbers on far left > Edit > Delete Row > remove filter > Delete helper colC

Hi

When you are in the sheet, press ALT F11. This will bring up the VBE. Go Insert, Module, paste in the code. Then go back to the spreadsheet, press ALT F8, select the macro name, then run.

HTH

rylo

You guys are great! It was a killer going through 16k leads deleting it line by line by line

I have a similar issue. However, I only have 1 column where the data point is duplicated. I need to run a script or macro or something that removes BOTH rows of any item that is duplicated.

Help, please?

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.