deleting like records

[Dan Wilson]

Good day. I'm running Excel 2007 on Windwos Vista Business 32 bit. I host a radio show and track my song requests using excel. I track the name and date of each request along with other information. Every year I compile a list of my callers to recognize them on the air. My workbook is now reaching a count of 2000 rows. I have been very diligent when entering names so that there aren't many differences on repeat callers. I have been copying the original worksheet to a duplicate worksheet to do sorting and deletion of duplicate names so that the final list shows each indicidual caller only once. This is becoming a task. Is there a way to have the second worksheet delete all but one row where the Name column is identical? Thanks in advance for any help.

 

[buxtongt]

I'm not sure what else you want to do with the data, but would throwing it all in a pivot table be an option? That should let you just see each name once. Hope this helps.

 

[VoG]

This may help http://www.mrexcel.com/tip138.shtml

 

[Dazzawm]

You will need to sort the relevant column in order then highlight the column the names are in.

nb Please try on a copy of your workbook.

Public Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim r As Long
Dim n As Long
Dim V As Variant
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))
Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
n = 0
For r = Rng.Rows.Count To 2 Step -1
If r Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(r, "#,##0")
End If
V = Rng.Cells(r, 1).Value
If V = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
Rng.Rows(r).EntireRow.Delete
n = n + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
n = n + 1
End If
End If
Next r
EndMacro:
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(n)
End Sub

 

[Dan Wilson]

Good day buxtongt, VoG and dazwm.

buxtongt - I'm sure that your solutioni will work, however, I know nothing about pivot tables. I will look into that option. Thank you...

VoG - thank you for responding, but your solution does not give me all the data from each row.

dazwm - your solution is the winner for me. It worked the first time I tried it and it did exactly what I wanted. Thank you!

My thanks to all who took the time to read my question.
Dan Wilson...

 

[Javi]

Just a question here.... Would the remove duplicates in excel 2007 not do the job?

 

[MARK858]

The remove duplicates in 2007 is "method 5" in the link VoG supplied