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

    advanced filter/uniique records only (xp-sp2)

    Hi All

    In my spreadsheet, column C contains names, some of which are duplicates.
    I've clicked on a cell in column C. Then did Data-Filter-Advanced Filter. Chose Filter in Place and checked Unique Records Only but duplicates are still showing.
    Any help as to why it's not working/what steps I've skipped would be greatly appreciated!

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: advanced filter/uniique records only (xp-sp2)

    Could there be invisible differences? For example, trailing spaces after some of the names? Excel considers <code>"Nancy "</code> to be different from <code>"Nancy"</code>. Also, the non-breaking space Alt+0160 is different from the normal space.

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

    Re: advanced filter/uniique records only (xp-sp2)

    No invisible differences - this was imported from a text file. They are center-aligned, if that makes any difference.
    I'm not sure what you mean by non-breaking space.

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

    Re: advanced filter/uniique records only (xp-sp2)

    It worked when I deleted all the columns after column C.
    Then after I do the filter, I undo and the deleted columns come back.
    I don't remember doing that last time but seems to work fine.

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: advanced filter/uniique records only (xp-sp2)

    The character with ASCII/ANSI code 160 looks like a space, but in cells with "wrap text" turned on, the text will not begin a new line after such a space. That's why it's called a non-breaking space: a line won't break there.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: advanced filter/uniique records only (xp-sp2)

    If you click on a cell in column C, the advanced filter will automatically include neighboring columns to determine what unique records are.

    If you select column C, or the used range in column C, the advanced filter will warn you; if you then click Yes, it will ignore neighboring columns. So you can avoid deleting and restoring columns.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: advanced filter/uniique records only (xp-sp2)

    Nancy

    This has been a problem close to my heart recently as I was doing a major clean up of some very old data. The problem occurred when I had to copy a swathe of information from an excel spreadsheet to a txt file. What I found was a "hidden" ascii character came accross with it (don't ask me which one as it is still a mystery).

    I have been playing with this as these "hidden" characters don't always come apparent. To see if they are there try putting in =len (A1) in an adjacent cell (assuming the string is in A1) and see if its length corresponds with the length that is visible. If not try using =trim (A1), this will get rid of any superfluous data in front and behind the string.

    Give me a shout as I am having a quiet day, pre-Christmas and have got time on my hands.
    Jerry

Posting Permissions

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