+ Reply to Thread
Results 1 to 4 of 4

Unique item filter stops working when Table column is sorted?

  1. #1
    Registered User
    Join Date
    06-18-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    21

    Unique item filter stops working when Table column is sorted?

    I have a formula setup that basically references a table column (day in this case) and finds all the unique day values
    =OFFSET(Table1[Day],0,0,COUNTA(Table1[Day])-2,1)

    This works great, UNTIL I sort that table column "Day" and then all goes wrong (it only finds one day and repeats it). How do I go about fixing this and/or making the formula smart enough to get all the unique days regardless of the table sort order?

    The Table is on sheet 1 and sheet 2 has the day place holders.

    Thanks for any help with this
    Attached Files Attached Files

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Unique item filter stops working when Table column is sorted?

    e2Excel,

    Using your provided sample file, this regular (non-array) formula should work for you. Delete the current formulas in 'Sheet1' column L and put this formula in 'Sheet1' cell L4 and copy down:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    06-18-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Unique item filter stops working when Table column is sorted?

    tigeravatar, awesome...works perfectly.
    Took me a minute (I'm rookie) to learn the difference between enter, ctrl + shift + enter...and simply just enter and then dragging down to get the values to show properly.
    Thanks again!!

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Unique item filter stops working when Table column is sorted?

    You're very welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Formatting stops working after adding column
    By rz6657 in forum Excel General
    Replies: 3
    Last Post: 05-21-2013, 06:57 PM
  2. Replies: 4
    Last Post: 09-15-2010, 08:45 PM
  3. unique record filter is not working only in this attached sheet
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-18-2008, 06:31 PM
  4. Indexing Down Sorted Column to Next Unique Number
    By longfisher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-02-2007, 07:45 PM
  5. [SOLVED] auto filter stops working
    By [email protected] in forum Excel General
    Replies: 2
    Last Post: 09-01-2005, 04:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1