create a list of different values from a table without repeats

Having multiple columns of names to check adds an additional step to the process. Here is one way to do it.

Formula in Table 2::A2 =INDEX(Table 1::B:D,MOD(ROW()−2,ROWS(Table 1::B,1))+2,INT((ROW()−2)/(ROWS(Table 1::B,1)))+1)

Fill down to complete the column

This formula goes through all the rows and columns to get all the names into one column.

You can add new rows to Table 1 and the formula will still work

You can add new columns to Table 1 IF you add them in the middle, not before B or after D.

Formula in Table 2::B2 =IFERROR(IF(AND(COUNTIF(A$2:A2,A2)<2,A2<>0),ROW(),999999),999999)

Fill down to complete the column

Formula in Table 2::C2 =IFERROR(INDEX(A,SMALL(B,ROW()−1)),"")

Fill down to complete the column

Hide column A and B when done.

Alternatively,

Table 2::B2 =IFERROR(IF(AND(COUNTIF(A$2:A2,A2)<2,A2<>0),A2," ")," ")

No need for column C

Create a filter for column B to show only rows where text is not a single space character.

Hide column A

Or, instead of enduring the hassle of setting up extra columns and formulas when you have to do this, and then having to go through the same thing all over again when you have to do it again somewhere else...

You can just select the cells with the duplicates (cells in single or multiple columns all work fine), choose Numbers > Services > Copy Distinct from your menu, click in the top cell where you want the "deduplicated" values to go and command-v or choose 'Paste and Match Style' from the menu. Done in a second or so.

The installation takes less than a minute. Just double-click the downloaded .workflow package from this link to a Copy Distinct Automator Service (Dropbox download). You may have to click a button giving permission at System Preferences > Security & Privacy. Also make sure Automator.app and Numbers.app are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

Once installed, this is "reusable" in any table or document that you are working on. Just a menu pick away.

This has saved me a lot of time over the years. It provides a functionality quite similar to that built into Excel.

SG

Just remembered something that makes it a little better. Not that you would have a name of "0" but, if you wanted to use the same idea for something else, a zero might be a real entry.

A2 =INDEX(Table 1::B:D,MOD(ROW()−2,ROWS(Table 1::B,1))+2,INT((ROW()−2)÷(ROWS(Table 1::B,1)))+1)&""

B2 =IFERROR(IF(AND(COUNTIF(A$2:A2,A2)<2,A2<>""),ROW(),999999),999999)

C2 =IFERROR(INDEX(A,SMALL(B,ROW()−1)),"")

or, the alternative method would use

B2 =IFERROR(IF(AND(COUNTIF(A$2:A2,A2)<2,A2<>""),A2," ")," ")

Nice.

Do you have a script that mimics Excel's Text to Columns?

Text to Columns Automator Service here. (Dropbox download)

SG

I hope you give the Automator Service a try too. It takes about a minute to set up and use. And you can reuse it in the future.

SG

Hi joey,

What you want is a list of distinct values (names) from a collection of values, some repeated.

Here's one method, using an auxiliary table to rearrange the values into a single column list, then index the first occurrence of each name.

The VU table, then uses that index to extract the distinct names from the list in AUX.

The formula shown below the tables is entered in A2 of the auxiliary table, AUX, then filled down to as many rows as needed to accommodate the full list of names. In the form shown, the formula will produce an error flag in rows of column A beyond the number of names listed on the Schedule table. Once the formula has been entered as shown, and is working correctly for rows where there is a name to be copied, that formula can be wrapped in IFERROR to suppress the error flag and allow adding rows to AUX for future expansion of the list.

AUX::A2: OFFSET(Schedule::$A$1,MOD(ROW()−2,ROWS(Schedule::$B,1))+1, QUOTIENT(ROW()−2,ROWS(Schedule::$B,1))+1)

Wrapped in IFERROR:

IFERROR(OFFSET(Schedule::$A$1,MOD(ROW()−2,ROWS(Schedule::$B,1))+1, QUOTIENT(ROW()−2,ROWS(Schedule::$B,1))+1),"")

Column B of AUX contains this formula, entered in B2, then filled down to the end of columnB.

AUX::B2: IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")

This inserts an index number marking the row where each name first occurs in the list in column A, and a null string ( "" ) in all other rows. he formula will echo any error flags in column A, but needs no changes to perform as intended with the revised column A formula shown.

Here are the Schedule and AUX tables with the revised formula, and enough spare rows to add two more columns to Schedule.

One column has been added, and filled with names, one of them new.

(I also narrowed the width of the columns for an easier fit in the space here.)

The VU table contains the formula shown below the tables, used to collect the distinct names from the full list in AUX:

VU::A2: IF(ROW()−1>MAX(Aux::B),"",INDEX(Aux::A,MATCH(ROW()−1,Aux::B,0)))

Regards,

Barry