Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Unique records only (XL97/WinNT4)

    If I have a large-ish worksheet (some 1600 rows by 17 columns), is there an easy way to get XL to check just column A and where it finds the value of two adjacent cells in two rows are the same, delete the second row (regardless of the content of the other columns)?

    And if not, how would I do this in VBA, please?

    Many thanks
    Beryl M


  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: Unique records only (XL97/WinNT4)

    Non-macro solution:
    You could add a column and fill it with:
    =a2=A1
    To get TRUE (same as previous) or False different from previous.
    Then use autofilter on this column, filter for true and then delete all the "visble rows"

    If you still need a macro that can be done also,

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique records only (XL97/WinNT4)

    Steve

    As Beryl wants to remove the second row where there are duplicates you must compare each cell with the one above it if you are going to remove TRUEs, so you should start =A2=A1 in cell B2 not in cell B1. See attached.

    Also a good idea to replace Col B with values only otherwise you have confusing #REFs in Col b after removing the TRUEs.

  4. #4
    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: Unique records only (XL97/WinNT4)

    You are correct. I assumed starting in B2, but did not state that. Thanks for making it explicit. Also I would then just delete the extra column (again, I wasn't explicit)

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique records only (XL97/WinNT4)

    If you want a code example, check out this page from Chip Pearson: http://www.cpearson.com/excel/deleting.htm...teDuplicateRows

Posting Permissions

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