Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to put records of same person on same row and delete duplica
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, -- Thank You! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to put records of same person on same row and delete duplica
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 -- Ken Hudson -- Thank You! "maijiuli" wrote: 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, -- Thank You! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to put records of same person on same row and delete dup
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 befo 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 -- Thank You! "maijiuli" wrote: 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 -- Ken Hudson -- Thank You! "maijiuli" wrote: 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, -- Thank You! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to keep 15 specific records and delete 1000 other rows. | Excel Programming | |||
Macro to delete duplicate records | Excel Programming | |||
Sorting and delete records with macro | Excel Programming | |||
Delete records using excel Macro | Excel Programming | |||
Delete records using excel Macro | Excel Programming |