deleting like records

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
437
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

buxtongt

Well-known Member
Joined
Oct 5, 2011
Messages
709
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.
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,409
Office Version
  1. 2016
Platform
  1. Windows
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.

Code:
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

Active Member
Joined
Feb 5, 2006
Messages
437
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
May 26, 2011
Messages
438
Just a question here.... Would the remove duplicates in excel 2007 not do the job?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
The remove duplicates in 2007 is "method 5" in the link VoG supplied
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,460
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top