Clearing Duplicate Entries
 

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

Clearing Duplicate Entries
 

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

"J. Trucking" skrev i meddelelsen
...
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