+ Reply to Thread
Results 1 to 7 of 7

"Unique" filter allowing duplicates

  1. 05-08-2015, 11:55 AM #1
    Ochimus
    Ochimus is offline
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,397

    "Unique" filter allowing duplicates

    Hope someone can see the flaw here.

    Attached file Duplicated Uniques.xlsm has letter combinations in Col A. Each "unique" combination needs to have a "Group number" assigned to it.

    The Macro below creates the list of "Unique" combinations in Col I, sorts them and creates sequential Group Numbers in Col J.

    But for some reason it allows two versions of one combination that you will see in Cells I18 and I19, and I can't see why?

    Please Login or Register  to view this content.
    All suggestions received gratefully

    Ochimus
    Reply With QuoteRegister To Reply
  2. 05-08-2015, 12:22 PM #2
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: "Unique" filter allowing duplicates

    A quick help, but not a full answer to the issue can be
    Please Login or Register  to view this content.
    Last edited by PCI; 05-08-2015 at 12:46 PM.
    - Battle without fear gives no glory - Just try
    Reply With QuoteRegister To Reply
  3. 05-08-2015, 12:27 PM #3
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: "Unique" filter allowing duplicates

    For some reason the advanced filter insists on working with a header included. So... you when you filter the range starting at row 2 (so skipping the real header), whatever the first record is will be copied both as a header and (if there is another instance), also as a unique value.

    I suggest you change this line:
    Please Login or Register  to view this content.
    to this (it will include the header in the copy to the unique list, but paste into I1 instead of 2, then since you sort I2 and down, your header will stay put and your list will be unique 
    Please Login or Register  to view this content.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.
    Reply With QuoteRegister To Reply
  4. 05-08-2015, 01:09 PM #4
    Ochimus
    Ochimus is offline
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,397

    Re: "Unique" filter allowing duplicates

    Arkadi,

    Many thanks for the prompt response and clear explanation.

    There MAY come a day when I get to the bottom of Excel's wondrous wrinkles - but it's looking increasingly unlikely

    Ochimus
    Reply With QuoteRegister To Reply
  5. 05-08-2015, 01:13 PM #5
    Ochimus
    Ochimus is offline
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,397

    Re: "Unique" filter allowing duplicates

    PCI,

    Not sure what happened, because when I opened the thread a few moments ago, your response just said "Wrong Answer".

    Just refreshed it, and now see your suggested approach, so many thanks for that as well,

    Ochimus
    Reply With QuoteRegister To Reply
  6. 05-08-2015, 01:22 PM #6
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: "Unique" filter allowing duplicates

    Well I proposed a wrong answer first and then got a new one.
    For the fun a remake of the macro.
    BTW it looks to me that the way your macro is preparing the group formula is done a bit too early and then the group is not correct
    "Sort by Col D " is done at the end and only for column I

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 05-08-2015, 01:41 PM #7
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: "Unique" filter allowing duplicates

    Ochimus,
    I have yet to meet someone who has achieved that goal.... though some are incredibly close (and I am certainly not one of them). Happy to have been of use
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Does the criteria for an Advance "OR" Filter needs to be composed of unique values?
    By LenaK in forum Excel General
    Replies: 6
    Last Post: 01-30-2015, 03:13 PM
  2. display unique values of a data range "n" columns by "n" rows
    By Jerlle in forum Excel General
    Replies: 0
    Last Post: 11-22-2013, 01:36 PM
  3. [SOLVED] Conditional formatting to highlight duplicates based on "Yes" or "No" in another column
    By BB1972 in forum Excel General
    Replies: 2
    Last Post: 08-17-2012, 05:10 AM
  4. Count the unique items in column F if E:E = "Chris" and M:M = "VIC" and N:N = "PT"
    By tyrese214 in forum Excel General
    Replies: 1
    Last Post: 07-16-2010, 02:44 AM
  5. "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du
    By TC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-11-2005, 10:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1