Seaech and compare 2 tables

Question

Level 1

4 points

Seaech and compare 2 tables

Hello,

I don’t know the specific words to explain what I’m looking for, so I’ve issues to find relevant topics for something probably simple.

I’ve a table (people of interest) with names (column A) and biographies (column B).

I’ve another table (family tree) full of names (sometimes 3 thousands). They are in several columns.

I’d like to compare the 2 tables and identify the names of biography table that are also in the family tree table. Using column A of biography table, searching in all columns of family table and then, if possible, display the results using column B of the biography table.

Example:

table biography, column A: Smith, Fraser, McIntosh. Column B: blacksmith, highlander, computer
table family has: Gates, Musk, Jobs, McIntosh, Wozniak
function adds in another table: computer (because McIntosh is there)

Each cell has a name and a date. The best would be to have one function looking fir the exact name+date, and another looking just for the name but that is probably too much.

So far I search individually for each name. I need half an hour to complete that task. I think with a function I could accomplish that in 1 second.

Thank you for your help. :)

Posted on Jun 2, 2022 5:54 AM

Reply

Answer 1

Badunit

Level 7

31,202 points

Jun 7, 2022 8:08 PM in response to Kognak16

The tables you showed don't quite match the description in your first post. It is a much easier task if the "family" table is a single column vs what I believe you described as "several columns" in your original post.

If the Family table is just one column you can add two more columns to it and use XLOOKUP to bring in the data from the Biographies table. Or you can do basically the same thing in your "result" table (column A would be the same list as column A of the Family table), then filter to show only those rows that have data. But is the Family table one column or several?

Reply

Answer 2

Badunit

Level 7

31,202 points

Jun 7, 2022 9:07 PM in response to Kognak16

Here are the two ways mentioned. I edited this and added some additional ideas at the bottom that might be bette depending on which table is longest.

The Results table needs to be as long or longer than the Family table. Best to make it longer than you expect the the Family table to ever be.

Formulas in the Results table are

cell A1 =IF(ROWS(Family::A)>ROWS(A),"NEED MORE ROWS","Name")

cells B1 and C1 are plain text

column A =XLOOKUP(B,Biographies::B,Biographies::A,"-",0)

column B =IFERROR(Family::A,"-")

column C =XLOOKUP(B,Biographies::B,Biographies::C,"-",0)

The filter for the table is shown (but is currently off). If you turn it on it will show only rows that have data in column A.

Cell A1 is checking that you have enough rows in the table.

Alternatively you can do without a Results table and instead add two new columns to the Family table and do the two lookups there.

Formulas in the Family Table are

Column B =XLOOKUP(A,Biographies::B,Biographies::A,"-",0)

Column C =XLOOKUP(A,Biographies::B,Biographies::C,"-",0)

One other way would be to add an additional column to the Biographies table that looks to see if the "family cell" is listed in the Family table with the result of TRUE or FALSE. Then a filter that shows only the TRUE rows. If the Biographies table is short and the Family table is very long, this would calculate faster. And a fourth alternative is to create a Results table like in the first example except using the Biographies table as the basis for it.

Reply

Answer 3

Ralf-F

Level 4

2,757 points

Jun 2, 2022 6:48 AM in response to Kognak16

Could you please post a screenshot of your current layout, I need to understand where you have data and how they look.

For me it is not 100% clear where the data are and where they should be shown as soon as you have a match.

Small example with a few rows will help, Thanks.

Ralf

Reply

Answer 4

Badunit

Level 7

31,202 points

Jun 7, 2022 9:23 PM in response to Badunit

Here is the fourth idea.

Formulas in Results table are

A1=IF(ROWS(Biographies::A)>ROWS(A),"NEED MORE ROWS","Name")

A =Biographies::A

B =Biographies::B

C =Biographies::C

D =COUNTIF(B,Family::A)>0

Filter is shown in the screenshot but is not on.

Hide column D after it is all set up

Reply

Answer 5

Kognak16 Author

Level 1

4 points

Jun 7, 2022 6:13 PM in response to Ralf-F

Hi,

sorry for the late answer.

Here's a screenshot:

Table Biographies is always the same (with dozen of names), for each work I've to do. Table Family will be different each time (with hundreds of names). I need a way to check if people are in both Biographies and Family.

I'm not even sure it's possible to use Numbers for this. And I've no idea which words I should use to describe what I need to achieve. :/

Thank you for your help.

Reply