Index only showing first instance, need to show both/all instances

I'm not sure how to phrase this. I am new to Numbers. I have created a "neighborhood phone book" database in Numbers. End result is 1) export to CSV, 2) Datamerge in Adobe InDesign. In Numbers I have one tab with all resident's info. In another tab I have created a "street index" which pulls info from the first tab and organizes by street to create an index. In the main residents tab there are some people with second marriages or people who live together and have different last names but live at the same address. That all works fine. There is ONE instance where it does not work. An adult daughter who lives with her parents. All same last name, all same address. The daughter's name comes up first by alpha in the residents tab. For some reason, in the street index, ONLY her name comes up, and not BOTH her name and her parents' names. Also for some reason it WORKS for "Urban" (same situation). Not really sure how to make this work and have the parents' info included in the index. Any help is appreciated.



Street Index results


[Edited by Moderator]

Posted on Jul 9, 2025 5:26 PM

Reply
3 replies
Sort By: 

Jul 9, 2025 6:14 PM in response to rcoda

Please post the formula(s) you are using now. It would be a good starting point.


It would also be helpful to see more of the table, to include the row numbers and column letters and the column (or table) that has the street index (the numbers in your results). I recommend making a copy of your document and using fake names and data rather than posting real data here.


It looks like there are other problems on other rows, like a "/" after a last name when there is only one last name in the result and the word "and" dangling at the end when there is only one first name in the result.

Reply

Jul 10, 2025 4:08 AM in response to rcoda

If you arrange your data table in the form of a database with one person/name per row rather than usng multiple columns for names, you can do something like this:


Formulas in Table 2 are:


cell A2 =UNIQUE(Table 1::A)

This formula spills down


cell A2 =IF(A2≠"",TEXTJOIN("/",TRUE,UNIQUE(FILTER(Table 1::B,Table 1::A=A2))) & " | "&TEXTJOIN(", ",TRUE,FILTER(Table 1::C,Table 1::A=A2)),"")

Fill down to complete the column


I don't know how familiar you are with the functions. The second formula has two similar parts, one for the last names and one for the first names. The two parts work similarly and then are concatenated together. First is a filter to make an array of all names that match the "address" for that row. With the array of last names it shortens that array to unique names so it does not return the same last name more than once. Then it uses TEXTJOIN to turn the array into a string. And then the formula concatenates those two strings.


You can edit the formula to use ", " instead of " | " as the separator between last and first names and to use " and " instead of " ," between the first names.




Reply

Jul 10, 2025 7:40 AM in response to rcoda

Or if you arrange your table so that the first names are in adjacent columns you can do this:



I moved the address column to D, sorted by last name, and changed the formula in Table 2::A2 to be

=SORT(UNIQUE(Table 1::D))


Table 2::B2 =IF(A2≠"",TEXTJOIN("/",TRUE,UNIQUE(FILTER(Table 1::A,Table 1::D=A2))) & " | "&TEXTJOIN(", ",1,FILTER(Table 1::B:C,Table 1::D=A2)),"")

fill down to complete the column



Reply

Index only showing first instance, need to show both/all instances

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.