Macro to put records of same person on same row and delete duplica

[maijiuli]

Hello,

I have a worksheet that contains employee SSN on column A but on column D
contains their Name and DOB. Example:

Col A Col C Col D
123456789 Name Steve Smith
123456789 DOB 5011970
999554444 Name Jon Smith
999554444 DOB 12151975

I would like to write a macro to find matching SSN in column A and then put
the DOB in the same row (column E) as the employee name. I will rename
column E DOB. Also, then I would like to delete the duplicate row (DOB row).
Loop until end of column A.

Thank you very much,

 

[maijiuli]

Hello,

Nevermind, I was able to modify the code below from Ken Hudson.

Thanks everyone,

MJ


Hi EJR,

Try the following code. Be sure to make a copy of your workbook first! After
moving the duplicate names, the code will delete the extra row. Post back if
you need to have this modified or if you need help setting up the macro.

Option Explicit
Dim Iloop As Double
Dim RowCount As Double
Sub MoveDupes()

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

RowCount = Cells(Rows.Count, "A").End(xlUp).Row
For Iloop = RowCount To 2 Step -1
If Cells(Iloop, "A") = Cells(Iloop - 1, "A") Then
Cells(Iloop - 1, "E") = Cells(Iloop, "B")
Cells(Iloop - 1, "F") = Cells(Iloop, "C")
Cells(Iloop - 1, "G") = Cells(Iloop, "D")
Rows(Iloop).Delete
End If
Next Iloop

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[maijiuli]

Hello,

Another question. I realized that some of the data does not always have
duplicate rows. Meaning sometimes there will only be one row for an
employee. Using the example I had before:

Col A Col C Col D
123456789 Name Steve Smith
123456789 DOB 5011970
999554444 Name Jon Smith
999554444 DOB 12151975

Now I see I have situations like this:

Col A Col C Col D
123456789 Name Steve Smith
123456789 DOB 5011970
987654321 Name Bob Johnson
555444333 DOB 10141980


Notice how for SSN 987654321 we have only EE name and for SSN 555444333 we
have only EE DOB. In these situations since I'm moving the DOB data to
column E I would like to move 555444333's DOB to column E, instead of leaving
it in column D where the names are located. Col C will always have the
"Name" or "DOB" code to identify what the row is.

Thank you for looking,

MJ