Duplicate Items In A Spreadsheet

Posted by Debbie Lewandowski on January 15, 2002 1:38 PM

How do I get rid of duplicate items listed in a column?
I need to delete part numbers that are repeated in a very large file. Example: There may be two work orders for the same part number. How can I get rid of part numbers that are repeated?

This is a shared file, with different people entering data into the bottom of it. They don't know, or care for that matter, to see if the data they are entering already exists. They just enter the part number. Is there a way I can highlight the entire column and delete the repeats? Does that make sense to anyone?

This would save time and make my workload look smaller.
Thanks in advance!
Deb

Posted by Chris D on January 15, 2002 1:49 PM

Am I reading this right ?

You still want to delete the info that Person A enters if the part number is the same as what Person B entered somehwere above ?

(or does your worksheet just comprise a single column?)

Posted by Deb on January 15, 2002 2:14 PM

Correct, I want to delete person B's data, because person A has already entered it. Multiple columns in the spread sheet. What I'm doing now is a data sort, highlighting all the columns, but the p/n column "A" is in ascending order. The duplicates appear on top of each other. I delete the repeats, then highlight the rest of the sheet by due date, which is in another column. This works for now, but takes time.

Posted by Nate Oliver on January 15, 2002 2:21 PM

You may want to sort and do a subtotal or pivot table. Then select the visible cells (alt+semicolon), copy and paste into a new workbook.....The following may also be of use to you:

http://www.mrexcel.com/tip035.shtml

Check out method 2

Posted by Iago on January 15, 2002 5:07 PM

Data validation .....

On the basis that prevention is better than cure, you might want to consider using Data Validation in future so that duplicate entries in Column A cannot be made :-

Select Column A
Go to Data>Validation>Settings
Allow : Custom
Formula : =COUNTIF($A$1:$A1,A1)=1

Posted by Nate on January 15, 2002 9:10 PM

Jacob's Ladder

Someone by the name of Jacob delivered this, as far as I know, it works...Place it in a standard module, where macros normally go...

Option Explicit

Sub DeleteDuplicates()

Application.ScreenUpdating = False

Dim x As Integer
Dim LastRow As Integer
Dim c As Range
Dim FirstAddress As String
Dim SearchValue As String
Dim Counter As Integer

LastRow = Range("A65536").End(xlUp).Row

For x = 1 To LastRow

SearchValue = Range("A" & x).Value

If SearchValue = "zzzdeletemezzz" Then
Else

With Range("A1:A" & LastRow)

Set c = .Find(what:=SearchValue, LookIn:=xlValues, lookat:=xlWhole)
FirstAddress = c.Address
Set c = .FindNext(c)

If c.Address = FirstAddress Then
Else
Range(c.Address).FormulaR1C1 = "zzzdeletemezzz"
Counter = Counter + 1
End If

End With

End If

Next x

x = 1

If Range("A" & x).Value = "zzzdeletemezzz" Then
Rows(x).Delete
LastRow = LastRow - 1
Else
x = x + 1
End If

Loop While x &LT; LastRow + 1

MsgBox (Counter & " duplicates have been deleted."), vbInformation, "Deletion Complete"

End Sub

Posted by Fluellen on January 15, 2002 11:26 PM

Here's a better one ......

Better one :-
15415a.html

Or, if there are a lot of rows to be processed, this one might(or might not) be quicker :-
:- 15439.html