Eliminating Duplicates 1

[Rhonda1223]

I'm exporting data into multiple spreadsheets and then I need to take that data and put it into a master spreadsheet. My problem is that when I put it into the master spreadsheet, I need to identify any duplicates. An example is a list of users. If I'm trying to put a user in the master spreadsheet that already exists, that record needs to be identified somehow. Any thoughts??

Thanks,
Rhonda

 

[xlhelp]

Hi, Rhonda!

Do you want to delete these entries or do you just want to identify them?

 

[Rhonda1223]

Actually, both. I have different users that need both.

 

[xlhelp]

Hi, Rhonda.

To delete the duplicate entries, you need a macro. Rather than re-invent the wheel, here is some thing from Chip Pearson.

DeleteDuplicateRows

This macro will delete duplicate rows in a range. To use, select a single-column range of cells, comprising the range of rows from which duplicates are to be deleted, e.g., C2:C99. To determine whether a row has duplicates, the values in the selected column are compared. Entire rows are not compared against one another. Only the selected column is used for comparison. When duplicate values are found in the active column, the first row remains, and all subsequent rows are deleted.

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

You can find this macro and more at

http://www.cpearson.com/excel/deleting.htm

To be able to identify duplicates etc, we will make some assumptions. If your data is in a2:a1000 then in cell B2 you can enter a formula

=COUNTIF(A2:$A$1000,A2)

and then copy it down to B1000. Any cells that have duplicates (or more) will show up as 2, 3, 4 etc..