deleting duplicate cells in a column

[lkanarow@umich]

HELP....I have a list of 300,000 e-mail addresses and I need to delete all duplicate e-mail addresses. I've already sorted the list into alphabetical order, but it will take way too long to go through and delete the duplicates manually. If anyone knows the way...pls...let me know.

 

[daleyman]

ok, open a module in the vba editor

1. Tools>Macro>VisualBasicEditor
2. right click anything in the left hand pane relating to the workbook you are using and do "Insert>Module"
3. copy and paste the following code into the module...


Sub delete_duplicates()
rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
If UCase(Cells(rowx, 1).Value) = UCase(Cells(rowx + 1, 1).Value) Then
Cells(rowx + 1, 1).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop
End Sub




4. select the sheet that has email addresses from cell A1 down through column A, then activate the macro using [Tools>Macro>Macros...] and select the one called delete_duplicates.

 

[daleyman]

PS: assumptions are:

1.email addresses have already been sorted in alphabetical order, rendering the duplicates as neighbours.
2. list is unbroken, ie. no blank cells in list
3. list starts at cell A1 and runs vertically down through column A.

 

[Dave Hawley]

Hi

You say 300,000 ? is that correct? Excel only has 65536 rows in total. Are they spanned across multiple Columns?

 

[daleyman]

ouch, yes he has a good point there.

The macro above will delete entire rows for matchs found in column A, if the addresses are on several columns split each column onto a separate sheet first, then run the macro on each sheet.

 

[lkanarow@umich]

thanks for the help...one more question though.

If I split the list into seperate sheets to keep the addresses in column A and run the macro in each list, will it eliminate duplicates throughout the entire list of 300,000 or just on each sheet?

For example, is it possible that an e-mail address on sheet 1 could appear again on sheet 2 and/or sheet 3?

 

[Aladin Akyurek]

On 2002-03-31 23:20, lkanarow@umich wrote:
thanks for the help...one more question though.

If I split the list into seperate sheets to keep the addresses in column A and run the macro in each list, will it eliminate duplicates throughout the entire list of 300,000 or just on each sheet?

For example, is it possible that an e-mail address on sheet 1 could appear again on sheet 2 and/or sheet 3?

Did you consider using Access intead?