Excel macro to delete double entries in names?

Question

I have an excel 2007 table. In the B column it has names. The double entries are next to each other. I need to delete the double entries in the whole B column. NOTE:some names have capital letters and some not. E.G. JAMES Cook, James Cook, james cook. In fact, all three are doubles and I need to leave just one James Cook there. The table has A,B,C,D,E,F columns filled with other data.

Answer 1

Enter this formula in cell G1:



=IF(LOWER(B1)=LOWER(B2),1,0)



Then copy the formula down column G to your last row of data.



Notice column G will have a 1 for every name that is a duplicate regardless of capitalization.



Then copy column G, then Paste Special / As Values back into column G. This will let you sort by column G without recalculating the duplicate flag.



Now sort all of your data by column G. All of the unique names will appear at the top, and the bottom will all be duplicates. Highlight and delete all the rows with column G=1.



It's not a macro, but it'll get the job done.

Answer 2

Here is another entry into the fray....



This macro will delete duplicates for all 'case' structures; upper, lower, and proper.



Copy to the clipboard.



Sub DeleteDups_B()

Dim LastRow

LastRow = Range("B" & _

ActiveSheet.Rows.Count).End(xlUp).Row

For Each cell In Range("B2:B" & LastRow)

cell.Select

If Application.CountIf(Range("B2:B" & LastRow), _

LCase(ActiveCell.Value)) > 1 Or _

Application.CountIf(Range("B2:B" & LastRow), _

UCase(ActiveCell.Value)) > 1 Or _

Application.CountIf(Range("B2:B" & LastRow), _

Application.Proper(ActiveCell.Value)) > 1 Then

ActiveCell.EntireRow.Delete

End If

Next

End Sub



Right click on the appropriate sheet tab and select 'View Code'.



Paste the macro into the module area to the right.



Close back to Excel and: Tools > Macro > Macros and 'RUN'.

Answer 3

Open VB or Visual Basic Editor when in excel. Click on worksheet or name of workbook, or you can just add use the following in a new open module.



Sub sj()

Dim i As Long, n As Long



n = Range("a1").End(xlDown).Row



' put an index in column B to restore order later

Cells(1, "B") = 1

Cells(2, "B") = 2

Range("B1:B2").AutoFill Destination:=Range("B1:B" & n)



' sort column A ascending

Range("A1:B" & n).Sort Key1:=Cells(1, "A"), Header:=xlNo



' delete duplicates from bottom

For i = n To 2 Step -1

If Cells(i, "A") = Cells(i - 1, "A") Then Rows(i).Delete

Next



' restore orginal order and delete column B

Range("A1:B1").End(xlDown).Sort Key1:=Cells(1, "B"), Header:=xlNo

Columns("B").Delete



End Sub



Once done, close VB, go to macros name the macro, apply and run. save and then you should be good to go

Answer 4

This deletes the rows with duplicate names in column B and leaves only one single name. The duplicate names have to be adjacent to one another.



Sub Delete_Duplicates()

Dim r As Long

For r = [B65536].End(xlUp).Row To 2 Step -1

If LCase(Cells(r, "B").Value) = LCase(Cells(r - 1, "B").Value) Then

Rows(r).Delete

End If: Next r

End Sub

Answer 5

java script tutorial Free tips and tricks. Online Training Access

Technology tutorial Technology Shopping Jobs Dating

Education Fitness Gifts Internet Money



http://mozilazine.com/

Answer 6

You should use a database to do that. MySQL, Postgres, OpenOffice, and even MS Access would be better free solutions.