+ Reply to Thread
Results 1 to 16 of 16

Filter not returning unique values

  1. #1
    Registered User
    Join Date
    04-27-2009
    Location
    Walla Walla, WA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Filter not returning unique values

    I'm using Excel 2003 and am trying to filter a list of values. The list has duplicate values in it. When I use Filter>Advanced Filter and select "Unique records only" the filter only eliminates some of the duplicates. If there are 4 values of 100, it may filter 2 of them or none at all. Ultimately I'm trying to end up with a list of values with no repeats. Any help is appreciated.

    Thanks

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Filter not returning unique values

    example workbook would be a help.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    04-27-2009
    Location
    Walla Walla, WA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Filter not returning unique values

    Sorry, Hope this helps. The first column in my original data, the second column is filtered. Around 63 you can see that some duplicates are filtered and others are not.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Filter not returning unique values

    cant see a problem

    91.48 91.49
    91.49 91.51
    91.51 91.51
    91.51 91.52
    91.52 91.52
    91.52 91.53
    those are unique 91.51 91.51
    is not repeated in any other row.
    it doesnt filter on match across columns
    9.51 is returned as unique for col c but that means it must also show everyhing in the same row
    if you want to filter duplicates like that
    copy column c below column a delete row c
    then filter unique on col a
    Last edited by martindwilson; 04-27-2009 at 08:27 PM.

  5. #5
    Registered User
    Join Date
    04-28-2009
    Location
    Chicago, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Filter not returning unique values

    martindwilson,

    I'm not sure I understand your answer, but I think I'm seeing a different problem with my VBA script and AdvancedFilter. The following code snippet:

    Set OutputRange = Range("G2").Resize(NumVI, 1)
    Set InputRange = Range("I2").Resize(NumVI, 1)
    InputRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=OutputRange, Unique:=True
    On this data range:

    sourang
    stevemo
    sourang
    jamesm
    sourang
    stevemo
    sourang
    anilrudr
    sourang
    ritupup
    chrisstr
    howard
    howard
    jamesm
    ognjend
    harikrish
    sourang
    gadikos
    gadikos
    gilleve
    gadikos
    produces:

    sourang
    stevemo
    sourang
    jamesm
    anilrudr
    ritupup
    chrisstr
    howard
    ognjend
    harikrish
    gadikos
    gilleve
    I would assume that "sourang" should not be see twice in my list. Do I not understand what "AdvancedFilter...Unique:=True" are supposed to do?

    Thanks,
    Matt

  6. #6
    Registered User
    Join Date
    04-28-2009
    Location
    Chicago, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Filter not returning unique values

    BTW, I forgot to mention I am using 2007.

    While I imagine this should be in the programming forum, but I didn't want to move what was essentially a follow-up question.

  7. #7
    Registered User
    Join Date
    04-27-2009
    Location
    Walla Walla, WA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Filter not returning unique values

    I guess I'm not explaining myself well enough. The first column is my original list of numbers. The second column is what that list returns when it's filtered. If column A has 91.51 with another 91.51 below it as well, I only want to see 91.51 once in my filtered column. I'm looking to eliminate any duplicate numbers.

    Thanks

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Filter not returning unique values

    Regarding the 'sourang' problem, got any trailing spaces?

    Regarding the numerical values, are they exactly the same, or just to the two decimals shown?
    Entia non sunt multiplicanda sine necessitate

  9. #9
    Registered User
    Join Date
    04-27-2009
    Location
    Walla Walla, WA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Filter not returning unique values

    In some cases the numbers are identical out to 13 decimal places. Thanks for the lightbulb moment though, not all of my dupes are actually dupes.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Filter not returning unique values

    Duped by non-dupes!

  11. #11
    Registered User
    Join Date
    04-28-2009
    Location
    Chicago, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Filter not returning unique values

    Quote Originally Posted by shg View Post
    Regarding the 'sourang' problem, got any trailing spaces?
    No trailing spaces, I've checked. And the update, from further research is that only the first record is duplicated. It shows up first, which it should, and then shows up a second time when it appears again, but not for the third and beyond, and no other record shows the same behavior.

    So if I delete the first 'sourang' in the source list, 'stevemo' inherits the problem.

    Any more ideas? I'm stumped, except to say that it appears to be a clear Excel problem with AdvancedFilter and Unique, but why wouldn't it be a more well-known problem if that were the case?

  12. #12
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Filter not returning unique values

    need to see the worksheet

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Filter not returning unique values

    You need to have a header row that is not part of the data.

  14. #14
    Registered User
    Join Date
    04-28-2009
    Location
    Chicago, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Filter not returning unique values

    You need to have a header row that is not part of the data.
    shg, that was exactly right. Why is the header row needed?

  15. #15
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Filter not returning unique values

    coz otherwise it will treat the first line as a header row and it wont be included in the filter

  16. #16
    Registered User
    Join Date
    04-28-2009
    Location
    Chicago, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Filter not returning unique values

    coz otherwise it will treat the first line as a header row and it wont be included in the filter
    Okay, so file that last question under the "Matt Is Stupid" category.

    Thanks for the help, I didn't know it included a header row. Any chance there is a "HasHeader" option I can set to false? either way, I'm good now.

    Thanks again.

+ 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