A Numbers ‘Index’?

Question

Level 1

8 points

A Numbers ‘Index’?

Here’s the story: I have several different tables, each with a column of names. Some of the names in those tables repeat.

What I would like to do is to create another table with a column that somehow compiles all the names from the other tables, filters out the repetition, and sorts them.

In fact, because I’m shooting for the moon here, it would be great if there were some way for this compilation (or index) table dynamically adjusted to other tables so that if names were added or subtracted in source columns, rows would likewise increase or decrease automatically.

Is any of that possible, or would you be able to steer me in a direction of functions that could do something even like that?

Thanks!

Macbook (2016 or later)

Posted on Jan 22, 2019 8:59 PM

Reply

Answer 1

SGIII

Level 8

37,562 points

Jan 28, 2019 8:27 PM in response to Cody_EG

Nice use of UNION.RANGES().

As you point out, Numbers only supports a few types of array formulas. This is one that it can't support.

However, there are semi-automated approaches that can eliminate some of the drudgery.

You could, for example, use your UNION.RANGES() approach to collect the column values in one table. Then you can quickly eliminate the duplicates and alphabetize using the Category feature like this.

You right-click on a label in the left column and choose 'Collapse Peer Groups' in the contextual menu to get the table to look like the screenshot. Then select the A through G, command-c to copy, click in a cell in a separate 'Index' table, and command-v or Edit > Paste and Match Format to paste the de-duplicated, alphabetized values.

If you have to do this very frequently then a script on the Mac could collect the column values from the tables you designate, and place them in a separate table. Post if you really need that. But in most cases, this semi-automated approach should be quite efficient.

(This was written with screenshots from the Mac but the approach in Numbers for iOS should be similar.)

SG

Reply

Answer 2

Cody_EG Author

Level 1

8 points

Jan 23, 2019 3:05 PM in response to SGIII

I've been working on the issue. If I put into Table 4 Column A, row 1 the formula "=INDEX(UNION.RANGES(FALSE,Table 1::A,Table 2::A,Table 3::A),1, ROW())", it will compile all the names from the source columns onto a single table. This gets me closer. Assuming column B, row 2 in Table 4, I also found an array function in excel that would filter out duplicates and alphabetize "=INDEX(A, MATCH(SMALL(IF(COUNTIF($B$1:B1, A)=0, COUNTIF(A, "<"&A), ""), 1), COUNTIF(A, "<"&A), 0))", but I don't think numbers supports this style array formula.

It's power in excel seems to come from the fact that it effectively converts text strings into unique sortable numbers (through the countif arrayed function)

Is there any other work around?

Reply

Answer 3

SGIII

Level 8

37,562 points

Jan 23, 2019 1:31 AM in response to Cody_EG

You should look into the INDIRECT function.

However, it is MUCH easier and more efficient to keep your like data together in one table and extract subsets and summaries from it using filters and formulas than it is to consolidate data scattered across different tables.

SG

Reply

Answer 4

Cody_EG Author

Level 1

8 points

Jan 23, 2019 11:38 AM in response to SGIII

Thanks, SGIII. I'll do a little exploration into the indirect function to see if I can work anything like what I hoped for. I hear what you're saying with keeping all of the data in one table for simplicity's sake. One other piece to the puzzle I'm trying to solve? These tables are all actually embedded in Pages document. They help to summarize information within sections of the document. I essentially would like to have a concluding table that refers to all those tables within the sections and is able to index all the names in each of the tables. Does that change how you would approach the issue at all?

Also, if you have time and opportunity: how would you write the formula to do something like what I'm looking for (compiling the information for an entire column--in theory one that could change in total rows--eliminate repetition and sort them) if it were all in just one table? I think even that will help get me thinking.

Thanks so much!

Reply

Answer 5

Cody_EG Author

Level 1

8 points

Jan 23, 2019 11:43 AM in response to Cody_EG

And, just in case it makes it easier to visualize what I would be hoping for:

Granted:

Table 1

Column A

A

B

C

Table 2

Column A

B

D

E

Table 3

Column A

A

F

G

I essentially want

Table 4

Column A

A

B

C

D

E

F

G

And ideally that column's formula would adjust the total rows to reflect the total amount of unique names in the source tables.

Reply

A Numbers ‘Index’?

Similar questions