Deleting duplicate cells that have been pasted in

Posted by Cherice on January 17, 2001 4:44 PM

I am working on several fraud cases involving calling cards and phone numbers. When I download the customer's bill all phone numbers are listed in an excel spreadsheet. I then want to pull each number called or dialed from only once and remove all duplicate entries. Most easily, I would like to set up one spreadsheet, then paste the call detail in and have it either highlight or delet all duplicate entries. I set up a validation rule last night which worked fine when typing new entries but when cells were pasted into the sheet, it did not detect them. Any help would be greatly appreciated :)

Posted by Dave Hawley on January 17, 2001 7:50 PM

Hi Cherice

There a number of ways this could be done.

1. With Data>Filter>Advanced Filter and filter for unique values only.

2. With the aid of Data>Sort and go through manually. Only really applicable for small lists that need checking before deleting.

3. With the Aid of Format>Conditional Formating and using a simple COUNTIF formula.

4. Totally automatic whenever new data is entered on your sheet via the Sheet_Change Event.

5. Totally automatic whenever you save via the Workbook_BeforeSave Event. The example below is for this one. To put is in, right click on the Sheet picture top left next to "File" and select "View Code", paste the code below over the top of what you see:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Cell As Range
Dim Cols As Range
Set Cols = Sheets("Sheet1").UsedRange.Columns("A:B")
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
With Cols

For Each Cell In .Cells
If WorksheetFunction.CountIf(Cols, Cell) > 1 Then
Cell.EntireRow.Clear
End If
Next

.SpecialCells(xlCellTypeBlanks).Cells.Delete
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
End With
End Sub

Change the Columns and sheet name to suit. Push Alt+Q to return to Excel.

I have some examples of Conditional Formatting for duplicates on my web page: http://www.ozgrid.com under the link "Handy Hints"

Hope this helps

Dave

OzGrid Business Applications

Posted by Cherice on January 18, 2001 5:20 AM

Dave,

Your page was excellent and easy to understand. I appreciate your reply!

Cherice