deleting duplicate cells in a column

lkanarow@umich · Mar 29th, 2002, 10:54 PM

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 · Mar 29th, 2002, 11:06 PM

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 · Mar 29th, 2002, 11:08 PM

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 · Mar 29th, 2002, 11:19 PM

Hi

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

daleyman · Mar 29th, 2002, 11:21 PM

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 · Apr 1st, 2002, 12:20 AM

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 · Apr 1st, 2002, 02:29 AM

Quote:

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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 10:54 PM	#1
lkanarow@umich New Member Join Date: Mar 2002 Location: California Posts: 6	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.

Mar 29th, 2002, 11:06 PM	#2
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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.

Mar 29th, 2002, 11:08 PM	#3
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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.

Mar 29th, 2002, 11:19 PM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi You say 300,000 ? is that correct? Excel only has 65536 rows in total. Are they spanned across multiple Columns?

Mar 29th, 2002, 11:21 PM	#5
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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.

Apr 1st, 2002, 12:20 AM	#6
lkanarow@umich New Member Join Date: Mar 2002 Location: California Posts: 6	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?