none
Extracting the collection of unique values from a filter in Excel RRS feed

  • Question

  • I have a file which has rows extending to tens of thousands across 8 columns. One particular column contains the weekend date. I have to count the number of weekends present in this file.

    Is there a way to extract this collection of unique values (of that column), by using the AutoFilter object?

    In case the question is vague:

    think about the drop-down list which comes up when we apply a filter on a particular column. That list must be represent a collection of some kind internally, I guess. If we can extract and get the count of this collection, then the problem is solved.

    Please help.

    Thanks in advance!


    Saturday, August 8, 2015 8:29 AM

All replies

  • Let's say the dates are in F2:F10000.

    If you only want to count the number of unique dates, you can use the following array formula, confirmed with Ctrl+Shift+Enter:

    =SUM(1/COUNTIF(F1:F10000,F1:F10000))

    This will fail if there are blanks in the date column. You can then use (also confirmed with Ctrl+Shift+Enter):

    =IF(F1:F10000<>"",SUM(1/COUNTIF(F1:F10000,F1:F10000)))

    If you want a list of unique dates, you can use Advanced Filter:

    • Copy the field name (column heading) of the date column to another cell on the same sheet. There shouldn't be any data below this cell.
    • Click anywhere in your data.
    • On the Data tab of the ribbon, in the Sort & Filter group, click Advanced.
    • Excel will select the entire data table and display the Advanced Filter dialog.
    • Select 'Copy to another location' under Action.
    • Click in the 'Copy to:' box and point to the cell where you copied the field name.
    • Tick the check box 'Unique records only'.
    • Click OK.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, August 8, 2015 9:03 AM