Forum Discussion
UNIQUE function on table with filters
WillSmyth See attached. If that's not what you want, I don't know. Note that I filtered the table to show only items from 2023.
By the way, better to place the SORT(UNIQUE(FILTER(....))) formula in another sheet to avoid it from being hidden in case the filter hides the top rows of the sheet.
WillSmyth You could add a column to the blue table called "filtered" with the following formula:
=AGGREGATE(2,3,[@[TX_DATE]])
That will put a 1 on all rows shown by the filter and 0 on all rows that are not visible.
Then the formula in L2 could be:
=SORT(UNIQUE(FILTER(Table_ExternalData_1[ITEM],Table_ExternalData_1[filtered]=1)))
I have added the new column FILTERED with the formula =AGGREGATE(2,3,[@[TX_DATE]])
The FILTERED column displays value 0 regardless of the filter set on the table.
- I think I have resolved the =AGGREGATE(2,3,[@[TX_DATE]]) formula with all visible rows showing . (assuming hidden rows will be a 0)
However entering the formula in L2 does not appear to work. Would you be able to give example in provided worksheet. ThanksWillSmyth But the formula in L2 seems to work for me.
WillSmyth See attached. If that's not what you want, I don't know. Note that I filtered the table to show only items from 2023.
By the way, better to place the SORT(UNIQUE(FILTER(....))) formula in another sheet to avoid it from being hidden in case the filter hides the top rows of the sheet.