I need to remove duplicates

[Chaos43]

I have a customer database that is sorted by phone number. How do I remove duplicate rows quickly and easily?

 

[Joe MAyo]

okay so what you need to do is copy that code i posted, then go back to your excel document and hit (alt F11) to access VBA , then go up to the toolbar click insert then click module, paste the code in , then hit (alt F11) again to return back to spreadsheet then go to tools look for MACRO
then click the play button its at the top MACROS.

 

[Chaos43]

so are you saying that if phone numbers match somwhere throughout that data? Then delete the duplicate rows

yes, that's is EXACTLY what i need.

Hi,

I beleive you may be able to simply select the phone numbers column (COL A) then click the Data>Filters>Advanced Filters menu, now select the unique values only tick box, this will strip out all the duplicates in column A.

Hope that helps,

Chris

in doing this, chris, it only deletes the phone numbers. it does not delete the corresponding information that goes along with those phone numbers...

 

[Joe MAyo]

Chaos do exactly what i said on my previous post it will work

 

[ChrisChard]

Ok my apologies then, thanks for clarifying this for me.

Cheers,
Chris

 

[jindon]

try
1) Hit Alt + F11
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 again
4) hit Alt + F8, select "test" then click on [Run]

Sub test()
Rows(1).Insert
Columns(1).Insert
Range("a1").Value = "x"
With Range("b1", Range("b" & Rows.Count).End(xlUp)).Offset(,-1)
    .Formula = "=if(countif(b$1:b1,b1)>1,1,"""")"
    .AutoFilter field:=1, criteria1:= "=1"
    .SpecialCells(12).EntireRow.Delete
End With
Columns(1).Delete
End Sub

 

[Joe MAyo]

mvp good look, yah that code that i pre-posted might not have worked if the dups where seperated. This will def work

Sub remove_duplicates_in_row()
Dim Rng As Range, _
LstRw As Long, _
i As Long
Application.ScreenUpdating = False
LstRw = Cells(Rows.Count, 1).End(xlUp).row
Set Rng = Range(Cells(1, 1), Cells(LstRw, 1))
For i = LstRw To 1 Step -1
If Application.WorksheetFunction.CountIf(Rng, Cells(i, 1)) > 1 Then _
Cells(i, 1).EntireRow.Delete Shift:=xlUp
Next i
Application.ScreenUpdating = True
End Sub

 

[Chaos43]

YOU GUYS ARE
LIFESAVERS!!!!!!!!!!!!



<br /><br /><br />

Thank You sooooooooooooooooooooooooooooo much!!!!!!!!
thank you thank you thank you
<br /><br />
ray:ray: i'm not worthy hehe

<br /><br /><br />

thank you

 

[Chaos43]

wow you guys totally rock!
<br />
<br />
i tried each and every one of ALL of your guys' suggestions and they all worked!!
<br /><br />
you guys basically saved my job. hehe (honestly)
<br /><br />
so thank you again

 

[robinwhitt]

Will this code work with a report that has all of the data in col A? We are doing a software audit and I have two reports - one done a few months ago and one done today. I want to weed out duplicates between the two reports. I've copied the software names from the 2nd report into the 1st so that they're all listed in col A. Make sense??? Thank you

 

[robinwhitt]

Ok, so I answered my first question - yes, it does work...now...

I've removed the duplicates of the questionable software on the same sheet but now I have the known approved software on another sheet within the workbook.

since we re-ran the report today, the questionable list also includes some known good. Instead of going through and manually removing the known good from the questionable, can the code be modified to do this for me?

I would like to compare like this...with worksheet 1 being new report and worksheet 2 being known approved

If WS 1 contains any software listed in WS 2 - delete from WS 1