How do i create a subset of data in numbers 3.5

SGIII made a handy script to copy the unique items:

And for things like this you may find this Copy Distinct Automator Service (Dropbox download) useful.

Once you have this you can use the list in a separate table with sumif()

is it possible to automate this process such that when I add a row to Table 1, the service will run and create unique entries into Table 2? Otherwise, this solution speeds up my manual process, but does not enable me to create Table 2 entries without manually selecting the entries in Table 1.

Hi Martin,

I like Pivot Tables too. The service and SUMIF / SUMIFS are about as close as one can get to achieving the same thing in Numbers 3. The manual selection for me provides maximum flexibility and speed; I can extract distinct values from anywhere and them place them anywhere. If you have an unchanging table setup and need to do constant updates that make the quick manual updates burdensome, then a more dedicated script might be worthwhile. If you give more details on your setup I might be able to suggest one. Just as with Pivot Tables it wouldn't be fully automatic, though; you would have to "refresh" by making a menu choice or by hitting a keyboard shortcut.

There are also formula-based methods of extracting distinct values that typically involve adding a "helper" column to your data. These would refresh automatically but can be susceptible to problems when trying to sort. If you are interested in that approach someone here can post an example.

SG

Hello

For instance, you may try something like the following tables.

Table 1 (excerpt) A1 Surname A2 A A3 B A4 A A5 A B1 Pay B2 10 B3 20 B4 20 B5 10 C1 ordinal C2 =COUNTIF(A$2:A2,A2) C3 =COUNTIF(A$2:A3,A3) C4 =COUNTIF(A$2:A4,A4) C5 =COUNTIF(A$2:A5,A5)

Table 2 (excerpt) A1 Surname A2 =IFERROR(INDEX(Table 1::A,C2,1),"") A3 =IFERROR(INDEX(Table 1::A,C3,1),"") A4 =IFERROR(INDEX(Table 1::A,C4,1),"") A5 =IFERROR(INDEX(Table 1::A,C5,1),"") B1 Total Pay B2 =IF(A<>"",SUMIF(Table 1::A,A2,Table 1::B),"") B3 =IF(A<>"",SUMIF(Table 1::A,A3,Table 1::B),"") B4 =IF(A<>"",SUMIF(Table 1::A,A4,Table 1::B),"") B5 =IF(A<>"",SUMIF(Table 1::A,A5,Table 1::B),"") C1 index C2 =IFERROR(MATCH(1,Table 1::C,0),"") C3 =IFERROR(MATCH(1,OFFSET(Table 1::C,C2,0,ROWS(Table 1::C)-C2,1),0)+C2,"") C4 =IFERROR(MATCH(1,OFFSET(Table 1::C,C3,0,ROWS(Table 1::C)-C3,1),0)+C3,"") C5 =IFERROR(MATCH(1,OFFSET(Table 1::C,C4,0,ROWS(Table 1::C)-C4,1),0)+C4,"")

Notes.

Table 1::C is to record ordinal number per distinct name in Table 1::A.

Table 2::C is to calculate row indices in Table 1::C where its value = 1, i.e., row indices of the 1st appearence of every distinct name in Table 1::A.

Formula in Table 1::C2 can be filled down.

Formula in Table 2::C3 can be filled down.

Formulae in Table 2::A2 and Table 2::B2 can be filled down.

Tables are built in Numbers v2.

Hope this may help,

H

EDIT: fixed notes.

use decimal option at your tool bar reference/pargraph,ecetra .

There is also a formula-based approach that requires only one extra column, like this:

In C2 of Table 1, copied down the column:

=IF(COUNTIF(A$1:A2,A2)=1,MAX(C$1:C1)+1,"")

In A2 of Table 2, copied down the column:

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

In B2 of Table 2, copied down the column:

=SUMIF(Table 1::$A,A2,Table 1::$B)

These approaches work. But I find it quicker and easier to use the Copy Distinct Automator Service. Just select cells in Table 1's column A, choose, the service from the Numbers > Service menu, click once in cell A2 of Table 2, and command-v to paste.

SG

Can I be confident the Service will continue to function for a good while into the future because I like the solution and want to ensure it's good for me into the future.

You should be ok with the Service for the foreseeable future. If in the future Apple changes AppleScript support in Numbers in a way that breaks the script, then either I or others here will know how to modify the script. It isn't that elaborate; if it were, I wouldn't know how to code it!

(And going in the other direction, Hiroto could easily change it so that it works with Numbers 2.x; currently it's Numbers 3.x only).

The .workflow package that contains the script is accessible in Finder by holding down the option key, choosing Go from the menu and navigating to Library > Services. Doubleclick 'Copy Distinct.workflow' to open it in Automator and view the script. You can, of course, also delete the Service by going there and trashing it the way you would any other item in Finder.

SG