Currently Being ModeratedFeb 4, 2013 12:44 AM (in response to DirekRod)
Do you really want only the unique names (names that appear only once in the whole document are unique within that set), or are you actually asking about the distinct names within that set (a single copy of every name that appears in the set)? I'm assuming the latter.
Here's a straightforward solution, using an index column added to each table to hold a count of the distinct names, incrementing on the first occurrence of each name in the multi-table list. The index is used by a Lookup formula that returns the name associated with each number. MAX() and IF are used to determine which table should be searched fir each name.
Data 1 contains a single formula, entered in B2, then filled down to the end of that column.
Data 1 :: B2: =IF(COUNTIF($A$1:A2,A2)=1,MAX($B$1:B1)+1,"")
Data 2 contains two formulas:
Data 2 :: B1: =MAX(Data 1 :: B)
This retrieves the current count of distinct names from Data 1.
Data 2 :: B2: =IF(COUNTIF(Data 1 :: $A,A2)+COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")
Fill down to the end of column B.
The first COUNTIF counts any occurrences of the name in Data 1, the second continues the count as far as the current row in Data2. It the total is 1, the count of distinct names is incremented in that row.
Data 3 contains the same two formulas as Data 2. B1 is revised to collect the current MAX from Data 2::B1, B2 is expanded to include full counts of the name from Data 1 and Data 2, and to continue the count in the current table:
Data 3 :: B1: =MAX(Data 2 :: B)
Data 3 :: B2: =IF(COUNTIF(Data 1 :: $A,A2)+COUNTIF(Data 2 :: $A,A2)+COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")
Summary contains a single formula, entered in A2 and filled down to the end of column A:
Summary :: A2: =IF(ROW()-2<MAX(Data 1 :: $B),LOOKUP(ROW()-1,Data 1 :: $B,Data 1 :: $A),IF(ROW()-2<MAX(Data 2 :: $B),LOOKUP(ROW()-1,Data 2 :: $B,Data 2 :: $A),IF(ROW()-2<MAX(Data 3 :: $B),LOOKUP(ROW()-1,Data 3 :: $B,Data 3 :: $A),"")))
The formula is a series of nested IF statements, with an associated LOOKUP statement.
IF(ROW()-2<MAX(Data 1 :: $B),LOOKUP(ROW()-1,Data 1 :: $B,Data 1 :: $A),NEXT
IF(ROW()-2<MAX(Data 2 :: $B),LOOKUP(ROW()-1,Data 2 :: $B,Data 2 :: $A),NEXT
IF(ROW()-2<MAX(Data 3 :: $B),LOOKUP(ROW()-1,Data 3 :: $B,Data 3 :: $A),NEXT
The first part of each checks if the current row number, -2, is less than the greatest index number in the current table. If that is TRUE, the LOOKUP part looks up the name corresponding to the current index number. If it's FALSE, control is passed on to the NEXT part.
The last part ( ""))) ) inserts a null string in rows not needed to display a distinct name.
The functions used are discussed, with further exaples of their use, in the iWork formulas and Functions User Guide. You can download the guide (and the Numbers '09 User Guide, also a very useful document for new (and experienced) users of Numbers) via the Help menu in Numbers '09.
Currently Being ModeratedFeb 4, 2013 11:28 AM (in response to Barry)
Thanks to your huge effort, really appreciated.
To be honest I am not "yet" a numbers-wiz person, but trying to, hehe. Not sure if this will give me what I need (please let me know if I understood my situation correctly), here's why:
Each of the existing table (JAN to MAY) contains list of the employees' names per month, some of the months have added new employees (FEB, APR & MAY) and/or removed separated employees (APR & MAY). The new table i am creating (ALPHALIST) will enlist all employees' names for the whole year, and hopefully, adding row/s automatically.
Hope that the attached sample data helps my situation to be more understandable for you, and most importantly, for you to take some of your time again just to help me on this.
Thank you again and best regards,