eliminate duplicate data entries.

[Castiglione]

I have a list of 10,000 email addresses in excel, column A. Many are duplicates. How do I eliminate the duplicates. Please be detailed in your step-by-step instructions, I do not know excel very well.
I have excell 2007.
Thanks
Jack

[solnajeff]

Hi

I have attached a small sample file to illustrate what is happening, essentially the code checks for the las occupied cell in column A and then counts to see if there are any matching occurrences. If a match is found the cell is deleted.

With the sample file simply click on the button to see it in action, or if you would like to see a little better how it works Click Alt+F11 and this will open the VBE window. You can see the code in module 1, place the cursor at any point in the code and Click F8, this will step through the code one line at a time so you can see step by step what is happening.

To use the code in your workbook click Alt+F11 and where you see the name of your file in the project window right click and select Insert/Module. Copy the code from the sample and you are good to go. You can start the macro from The VBE window or by clicking Alt+F8 and selecting "rem_dup" Run.


I hope that this is clear for you, please contact me if you need any more information.

Regards


Jeff

[Castiglione]

Hi Jeff,
First, thank you for your help. But I do not understand,,

You say....With the sample file simply click on the button to see it in action....

Which button?

I copied the file to my desktop, and opened it, I see the 15 email addresses, but what button do I click in have this work? I know nothing about macros or formulas or making them run.

Also, please email me directly at
[email protected], OK?
It may be easier to talk on the phone, and I can send you my phone number at my private address.'OK?

[Hollis27]

An easier way would be to create a pivot table and move the header into row format. See attached file.

[solnajeff]

Hi

An easier way without coding is to go to the "Data" ribbon, select Advanced in the Filter section and a box will pop up. Choose filter copy to another location, click on list range and select the range you wish to filter, select "Unique Record Only", select the destination cell and click OK.

You will need to delete the original data as this is left intact.

Hope this is a bit easier for you.

Regards


Jeff

[Chris Bode]

1.Right click on toolbar check the control box
2.Add a command button to your sheet
3.Double click the button to open code window and paste following codes
#
Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 1
col = 1

While Sheet1.Cells(row, col).Value <> ""
removeDuplicate row
row = row + 1
Wend
End Sub

Private Sub removeDuplicate(i As Integer)
Dim row As Integer, col As Integer
row = i + 1
col = 1

While Sheet1.Cells(row, col).Value <> ""
If Sheet1.Cells(i, col).Value = Sheet1.Cells(row, col).Value Then
Sheet1.Rows(i).Delete
row = row - 1
End If
row = row + 1
Wend
End Sub
#
Hope this works

Have a nice time

Chris

[Castiglione]

I am amazed. All you smart people, ... I just found the very simple and very direct answer to my own question. How to remove duplicate entries??
just go to the data ribbon, click data tools, click remove duplicates.

That's it.