Clearing Duplicate Entries

[J. Trucking]

Hello,

I have imported a whole bunch of data into Excel. It is employee data
and each entry will have the employees name in Column A. What I want
to do is make the cell contents of any duplicate entry blank so that I
end up with a whole bunch of entries for each employee with their name
only appearing in the first entry. If possible, I would like to then
merge the empty cells with the first one. For example, I currently
have:

John Doe
John Doe
John Doe
John Doe
Jane Smith
Jane Smith
Jane Smith
Jane Smith
Jane Smith

I would like to clear the contents of the 2nd, 3rd, and 4th "John Doe"
leaving only the first. I would then like to merge cells "A1:A4" to
group all of the John Doe entries. I could then like to do all of the
same with the Jane Doe Entries. I currently have some code but it
seems to hang up (A6 is the first employee name)

Do Until Range("A6").Value <> Range("A7").Value

Range("A6").End(xlDown).Select
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
ActiveCell.Value = ""
End If
Range("A6").Select

Loop

I have noticed that when I am in Excel, I can select all of the "John
Doe" names, right click, and Merge the Cells. It just gives me a
warning that the cells contain different values (I am referencing the
values from a different sheet). Maybe this is an easier way of dooing
it. Would there be a way I could select all similar entries and merge
them without seeing the warning message?

Thanks in advance,

John

 

[Per Jessen]

Hi John

This macro will clear multiple entries of names

Sub test()
Dim StartCell As Range
Dim EmpName As String
Dim off As Integer

Set StartCell = Range("A6")
off = 1
EmpName = StartCell.Value

Do Until EmpName = "" ' repeat until EOD
If EmpName = StartCell.Offset(off, 0).Value Then
StartCell.Offset(off, 0).ClearContents
Else
EmpName = StartCell.Offset(off, 0).Value
End If
off = off + 1
Loop
End Sub

I don't know how I would merge cells, as I haven't seen the data.
You could move data to empty cells in the row containing the name if that's
desireable.

Regards,
Per