Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: deleting duplicate cells in a column

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    California
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

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



  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    California
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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?




Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •