i have 3 tables (weekly reports),
they have employee names (vary by week) in one column, and a column of times (in minutes).
i would like to have a formula finding unique names in the first column and listing them in a new table as well as bring the correlated data from the time column.
see attached for reference
any help is much appreciated.
Numbers-OTHER, Mac OS X (10.6.7)
Hi,
Here's one way to do it using VLOOKUP and one auxiliary column (which may be hidden).
All data (not including the column headers) is collected from the other three tables, as is the data in column A of the three Time tables. Formulas are listed below.
Time 1::A2: =IF(COUNTIF($B$2:B2,B)=1,MAX($A$1:A1)+1,"")
Time 2::A2: =IF((COUNTIF($B$2:B2,B)+COUNTIF(Time 1 :: $B,B))=1,MAX(A$1:A1)+1,"")
Time 3::A2: =IF((COUNTIF($B$2:B2,B)+COUNTIF(Time 1 :: $B,B)+COUNTIF(Time 2 :: $B,B))=1,MAX($A$1:A1)+1,"")
Each is filled down to the last row in its table.
Time 2::A1: =MAX(Time 1 :: A)
Time 3::A1: =MAX(Time 2 :: A)
Summary:
A2: =IFERROR(VLOOKUP(ROW()-1,Time 1 :: $A:$B,2,FALSE),IFERROR(VLOOKUP(ROW()-1,Time 2 :: $A:$B,2,FALSE),IFERROR(VLOOKUP(ROW()-1,Time 3 :: $A:$B,2,FALSE),"")))
B2: =IFERROR(VLOOKUP($A,Time 1 :: $B:$C,2,FALSE),"")
C2: =IFERROR(VLOOKUP($A,Time 2 :: $B:$C,2,FALSE),"")
D2: =IFERROR(VLOOKUP($A,Time 3 :: $B:$C,2,FALSE),"")
Fill all four down to the last row of this table.
Descriptions of each of the functions used can be found in the iWork Formulas and Functions User Guide, which you can download through the Help menu in Numbers.
Regards,
Barry
Barry,
I see you resisted the temptation to inform the OP that while there are nine distinct names in the data, Name 9 us the only Unique one.
Jerry
2 columns, 3 tables, semi-conguent data
