Duplicate entries

[dancingbear72]

How do I weed out duplicates in an excel file?

Do I just sort them and look through or is there a way to pick them
out?

Cheers

 

[Peo Sjoblom]

Data>filter>advanced filter, unique records only and copy to another
location

 

[dancingbear72]

Thanks. When it says - "copy to" what do I put

 

[Ed]

What constitutes a "duplicate" in your data?

Are your duplicates just single-cell entries in one column? Is the column
sorted so all the duplicates would be next to each other? If you did that,
would it mess up our data?

Or are your duplicates the repetition of data across several cells? For
instance, if you had Last Name, First Name, and Phone No., would you have a
duplicate only if all three were repeated?

If you have the first instance - sorted single-cell entries in one column -
I have a macro I use that works well for me. It highlights the duplicates
in red.

Ed

 

[dancingbear72]

Ed

Yes, it's just one column. It's a list of names but there are thousands
of them. A macro that highlights them in red would be great, thanks.

 

[Peo Sjoblom]

Whatever you want, if the original is in A why not copy it to C,
also use a header..

 

[dancingbear72]

*What constitutes a "duplicate" in your data?

Are your duplicates just single-cell entries in one column? Is the
column
sorted so all the duplicates would be next to each other? If you did
that,
would it mess up our data?

Or are your duplicates the repetition of data across several cells?
For
instance, if you had Last Name, First Name, and Phone No., would you
have a
duplicate only if all three were repeated?

If you have the first instance - sorted single-cell entries in one
column -
I have a macro I use that works well for me. It highlights the
duplicates
in red.

Ed

message

Any chance of that Macro Ed, cheers.

Nothing else seems to work

 

[Ed]

Here's what I use. The first thing it does is select the first cell of the
column to be examined - I use Col. C; change to reflect your needs. This
column must be sorted either ascending or descending, so any duplicate
entries are together. The macro runs down the column - if it finds a set of
duplicates, the entry in the top cell is replaced with AAA, and the bottom
cell is filled in red. The AAA makes it easy to Edit>>Find or AutoFilter.
One of the last things it does is puts a formula below the last entry in
Col. D that counts how many duplicate entries were detected. Change if Col.
D isn't good for you.

Hope it works.
Ed



Sub FindDups()

'

' Select column

Range("C1").Select

ScreenUpdating = False



' Run down column and compare numbers

FirstItem = ActiveCell.Value

SecondItem = ActiveCell.Offset(1, 0).Value

Offsetcount = 1



' If the numbers are the same

Do While ActiveCell <> ""

If FirstItem = SecondItem Then



' Turns the second one red

ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)

' Replaces first cell value with AAA

ActiveCell.FormulaR1C1 = "AAA"

Offsetcount = Offsetcount + 1

SecondItem = ActiveCell.Offset(Offsetcount, 0).Value

Else

ActiveCell.Offset(Offsetcount, 0).Select

FirstItem = ActiveCell.Value

SecondItem = ActiveCell.Offset(1, 0).Value

Offsetcount = 1

End If

Loop

ScreenUpdating = True



'Puts formula to count AAA values in D column

ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=COUNTIF(C:C,AAA)"

ActiveCell.Select

ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],""AAA"")"

ActiveCell.Select



End Sub

 

[dancingbear72]

Sorry, one more thing. How do I run this?

 

[dancingbear72]

I managed to run it and it just came up with a 0 in column D. Did I do
something wrong?

 

[Peo Sjoblom]

Yes. Do as follows, make sure you have a header for your values so that if
the values start in A1 going to A100,
if that's the case select A1 and do insert>rows, if you already had a header
fine if not just type something
Select A1:A100, do data>filter>advanced filter, first check unique records
only, then check copy to another location
and when you do that click in the copy to box and then click on the first
cell where you want this filtered list to appear
then click OK

http://www.contextures.com/xladvfilter01.html#FilterUR