## Unique values into a new sheet

328 Views 2 Replies Latest reply: Feb 4, 2013 11:28 AM by DirekRod
Calculating status...
Currently Being Moderated
Feb 3, 2013 4:44 PM

Hi everyone!

I need help, I have many tables containing a list of names in different sheets. In a new sheet, I need to collect all the names from all the previous sheets, without repitition (or uniques values will just be collected to the new sheet).

Hope anyone can contribute a way.

Thanks and best regards to you all ;-))

Rod

iWork
• Level 7 (28,815 points)
Currently Being Moderated
Feb 4, 2013 12:44 AM (in response to DirekRod)

Hi Rod,

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.

Formulas:

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.

Regards,

Barry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.