+ Reply to Thread
Results 1 to 6 of 6

Flag if duplicates exist when list is auto-filtered

  1. #1
    Registered User
    Join Date
    11-14-2008
    Location
    Montreal, Quebec, Canada
    MS-Off Ver
    2003
    Posts
    36

    Question Flag if duplicates exist when list is auto-filtered

    Hi, all

    I have a column of entries, with a duplicates flag at the top. It indicates if there are duplicates present in the entries ("DUPLICATES"), and is blank ("") if there are no duplicates. It works well -- until I filter the list using an auto-filter. Because the formula looks at all entries, filtered and unfiltered, then if there are duplicates, the flag is raised, even if in the filtered list no duplicates are visible. I want the flag NOT to raise if the filtered list does NOT contain duplicates, AND to raise if the filtered list does contain duplicates.

    Normally I wouldn't filter on the column itself, but to keep the example file simple, I have done so.

    The first worksheet shows an unfiltered list with duplicates, with the duplicates flag raised.

    The second worksheet shows a filtered list without duplicates, but the duplicates flag is incorrectly raised, and should not.

    Can somebody suggest an improvement to this formula? Many thanks in advance.
    Last edited by NBVC; 11-24-2011 at 10:35 AM.

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

    Re: Flag if duplicates exist when list is auto-filtered

    please explain /show an example of filtered data that has dupes/no dupes
    "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
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: Flag if duplicates exist when list is auto-filtered

    in d8 dragged down
    =COUNTIF($C$8:$C$275,C8)
    in c5
    =IF(SUBTOTAL(3,D8:D276)<>SUBTOTAL(9,D8:D276),"duplicates","no duplicates")
    maybe (im using excel 97 at the moment you may need)
    =IF(SUBTOTAL(103,D8:D276)<>SUBTOTAL(109,D8:D276),"duplicates","no duplicates")
    Last edited by martindwilson; 11-10-2010 at 07:59 PM.

  4. #4
    Registered User
    Join Date
    11-14-2008
    Location
    Montreal, Quebec, Canada
    MS-Off Ver
    2003
    Posts
    36

    Re: Flag if duplicates exist when list is auto-filtered

    is there a way to do it without the helper column?

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

    Re: Flag if duplicates exist when list is auto-filtered

    why? helper columns are good, they uncomplicate things/can be hidden/(can be placed on another sheet out of sight but not for filter)
    Last edited by martindwilson; 11-15-2010 at 08:18 PM.

  6. #6
    Registered User
    Join Date
    11-14-2008
    Location
    Montreal, Quebec, Canada
    MS-Off Ver
    2003
    Posts
    36

    Re: Flag if duplicates exist when list is auto-filtered

    The answer is (from another spreadsheet, so references may not be correct, but you get the idea from the format):

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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