+ Reply to Thread
Results 1 to 5 of 5

Duplicates! Count, Remove, Highight, etc...

  1. #1
    Registered User
    Join Date
    11-20-2007
    Location
    Menlo Park, CA
    MS-Off Ver
    Office 2019 Pro Plus
    Posts
    33

    Duplicates! Count, Remove, Highight, etc...

    I have two lists. Each have 3 columns. First Name, Last Name, Email Address

    I can take the one workbook list, and add into the other to have one large list.

    1. I would like to highlight all the rows that have identical data.
    I'm using Excel 2007. I found a way to Highlight duplicates, but it only does it for one column (works great when highlingting duplicate emails). However, sometimes there are no email addresses listed, so I want to see the first & last name combo duplicates.

    Example: (Both would count as a duplicate)
    Ricky Henderson [email protected]
    Ricky Henderson [email protected]
    John Smith (Blank)
    John Smith (Blank)

    2. I would like to then count how many duplicates there are. (Above example being 2 duplicates)


    3. I then would like to have an option to delete all the duplicates.

    This would allow me to see who is duplicated, know how many were duplicated, and delete the duplications.

    Ill take an add-in, macro forumula, any suggestions you have. Thanks for the help!

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    I'm assuming that you have Last,First,e-mail in columns A,B,C

    1) Sort the combined list.
    Now the duplicates are next to each other.

    If they aren't already there, put headers on each column so you can use Advanced Filter.

    Select E1, press Delete.
    In E2, put =(COUNTIF($A:$A,A1)>1)
    Use Advanced Filter, with the criteria range E1:E2 (including the empty cell).

    This will show you all of the duplicates.
    Selecting and deleting will not affect the hidden rows in the middle of the selection.

    Counting duplicates is easiest done from the original two lists. One List is in G:I the other in J:L, the array formula {=SUM(COUNTIF($G:$G,J1:J7))} (entered with command-RETURN on Mac.) will return the number of duplicates.
    Note that the $G:$G range must use absolute referencing and the J range needs to be relative and finite (J:J won't work)
    This also assumes that each of the source lists has no duplicates in themselves.
    Last edited by mikerickson; 11-20-2007 at 10:01 PM.

  3. #3
    Registered User
    Join Date
    11-20-2007
    Location
    Menlo Park, CA
    MS-Off Ver
    Office 2019 Pro Plus
    Posts
    33
    I have over 5,000 entries. I do not want to have to manually delete.
    Column A = first Name
    Column b= Last Name
    Column c = Email
    Last edited by ImpetuousRacer; 11-21-2007 at 11:57 AM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    This is not "Deleting" the row.
    Let's see how you like it.
    Please Login or Register  to view this content.

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    The quotation mark after the question mark is missing.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1