Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    deleting non-duplicate rows (xp)

    Hi

    I have been searching the Excel postings for a previous post on this topic. I found some that were close but I wasn't sure if it was exactly what I needed.
    I have a worksheet with 25000+ rows. Column A contains last names. Some of the names are duplicated. I'd like to delete all other rows and keep the ones that have duplicates. (I'll end up with a list of pairs of names.)
    Can that be done with a macro?

    Thanks Much

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: deleting non-duplicate rows (xp)

    You can do it manually without a macro:
    In a blank column next to your data (eg Col Z, change as desired) enter in row 1 (assuming row 1 is header row) Occurrences
    In Row 2:
    <pre>=COUNTIF(A:A,A2)</pre>

    Autofill this down all 25000+ rows (select A2, move the mouse to the lower right corner [it will change to "+"] then dbl-click)
    This will give you the number of occurrences of that name

    Turn on Autofilter (data-filter-autofilter)
    In the pulldown of column Z choose 1
    Now only the "non-duplicated" names are displayed. Select all the rows, right click and choose "delete row"

    Then you can turn off autofiter (data - filter-uncheck autofilter) and you will have only the rows that the name is duplicated.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: deleting non-duplicate rows (xp)

    Perfect!!
    Even easier that I thought.
    Thanks

Posting Permissions

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