Hi Triklops,
Here's the same thing, done in Numbers.
The process relies on an added index column, which indexes, with a serial number, the first appearance of each item in the list.
There are two formulas involved, one in the index column, Column C of Table 1 (on the left), the other in Column A of the single column Table 2 (on the right).
Here are the tables again, with column C unhidden, and the formula in C2 shown in the Formula Editor below the tables.
There are five distinct items in the list, including the two, Mango and Kumquat, which are unique within the context of the table.
C2: IF(COUNTIF(B$2:B2,B2)=1,MAX(C$1:C1)+1,"")
The formula shown is entered in C2, and filled down to the end of column C.
COUNTIF counts the occurrences, from row 2 to 'this row' of the item in 'this row' of column B.
If the count is 1, IF calls MAX, MAX returns the MAXimum value in the cells in column C that are above 'this row', +1 adds one to that value, and the result is returned to 'this cell' by IF. If the count is not 1, IF skips the call to MAX, and returns a null string ( "" ), which makes 'this cell' appear 'blank'.
Table 2 contains this formula, entered in A2 and filled to the end of column A:
A2: IF(ROW()−1>MAX(Table 1::C),"",INDEX(Table 1::B,MATCH(ROW()−1,Table 1::C,0)))
The part in normal type, including the final parenthesis, is a switch that prevents calculation after the last item has been listed. The core part of the formula is shown in bold.
ROW()-1 returns a number one less than the number of 'this row' and hand it to MATCH as its search-for value.
MATCH searches for this value in column C of Table 1, and returns a number indicating the position of this value in the list in column 1. The value is handed to INDEX, where it serves as the row-index.
INDEX looks in column B of Table 1, and returns the value from the row indicated by MATCH.
"I want the list to be dynamic."
For a dynamic list, you will need to use formulas, such as the ones presented here. Your only manual intervention will be to add rows to Table 2 to ensure that there are enough to contain all of the distinct values to be listed.
If you can accept a requirement for manual intervention each time you need the list updated, it is also possible to extract the distinct values using an AppleScript (or an Automator Service containing an AppleScript). SGIII has written an Automator Service for this purpose. It does the job quickly and simply, but does not update automatically.
SG will likely be along within a day of your posting with a more detailed description.
Regards,
Barry
