Filter Table menu doesn't use a scrolling dropdown

Matthew,

I've been tripped up by that too. Only the first 25 show in the dropdown. But you can still use the filter inspector at the right when you have more than 25 distinct values.

SG

One way to get a list of distinct values is to use this Copy Distinct Automator Service (Dropbox download).

Doubleclick the .workflow file and it will install in your services menu (you may have to click 'download anway' in System Preferences > Privacy & Security) so it looks something like this:

That's a one-time installation. To use thereafter, just select the cells in the range from which you want to grab distinct values, make this menu pick, single-click the top cell where you want the disctinct values to appear, and command-v or option-shift-command-v to paste.

There are also formula-based ways to do this that involve setting up extra columns. They have the advantage of being "constantly refreshing" (whereas the service has to be triggered by making the menu pick and then pasting). If you need a constantly refreshing list, post.

SG

Hi SG,

could you give us an example of:

There are also formula-based ways to do this that involve setting up extra columns. They have the advantage of being "constantly refreshing" (whereas the service has to be triggered by making the menu pick and then pasting). If you need a constantly refreshing list, post.

Regards, Michael

Hi Michael,

Here's an example:

The formula in A2 of the second table, copied down:

=IFERROR(INDEX(Table 1::$A,MATCH(ROW()−1,Table 1::$B,0)),"")

The formula in B2 of the first table, copied down:

=IF(COUNTIF(A$1:A2,A2)=1,MAX(B$1:B1)+1,"")

SG