6

[Numbers, macOS]

I need to merge data in 2 spreadsheets / tables. Both of them contain contact information. The second table contains a column that I want to append to the first table.
Now, I can't just copy/paste the column, because even though the two tables contain the same column A, they don't contain an equal amount of rows.
For illustration, I put sample tables below.

Table #1

Name       Address          Phone
Mr. One    First Address    45120354554
Ms. Two    Second Address   42874518933
Mr. Three  Third Address    74125986538
Mr. Four   Fourth Address   95645740200

Table #2

Name       Address          Website
Mr. One    First Address    a@b.com
Mr. Three  Third Address    e@a.com

How could I append the Website column to table #1, so that if the Name column matches, it pastes what it finds in table #2's Website column to get the result shown below?

Target Table

Name       Address          Phone         Website
Mr. One    First Address    45120354554   a@b.com
Ms. Two    Second Address   42874518933   
Mr. Three  Third Address    74125986538   e@a.com
Mr. Four   Fourth Address   95645740200

I'm sorry if this is hard to understand. I don't really know how to put it, so feel free to ask any question!
Thanks in advance!

LinusGeffarth
  • 961
  • This operation is called a "Left Join" in database lingo. I don't know if it's possible directly in Numbers, but maybe at least knowing the term can help you search better. – shadowtalker Jan 10 '17 at 14:48
  • In fact I think the most robust solution would actually be to export your spreadsheets as CSVs, import them into a database (macOS comes with a database engine called SQLite already installed), do a join in the database, and then export a CSV to re-import into Numbers. – shadowtalker Jan 10 '17 at 14:50
  • I'm working on a Numbers-only solution, however. Is it possible that the Address columns disagree between tables? If so, how do you want to handle it? – shadowtalker Jan 10 '17 at 14:52
  • Technically that is not possible, no. What is possible, however, is that table #2 contains the "same" row multiple times. ("same" as in same name column) – LinusGeffarth Jan 10 '17 at 14:56
  • Actually, I'd not mind removing same columns except the name column obviously and leaving all the columns that table #2 contains, but table #1 does not. – LinusGeffarth Jan 10 '17 at 14:58

1 Answers1

7

Let's make some assumptions:

  1. The only column in Table 2 you care about is Website
  2. All of the data in both Name columns is 100% correct and can be matched "exactly" (i.e. not fuzzily)
  3. The columns are labeled with a "header" row (the dark gray background by default)
  4. The tables are named Table 1 and Table 2
  5. The data starts on row 2 in both tables

Therefore we are simply going to take the desired values from Table 2::Website (Mac Numbers formula notation for "Column Website in Table 2") and plop them into Table 1::D.

This is actually very easy to do, and it's one of those standard spreadsheet tricks that's great to have in your back pocket. An Internet search for something like "index match excel" and "vlookup excel" will give you tons of results.

Note for Microsoft Excel and Google Sheets users: write ! instead of ::, and put single-quotes ' around any table name with spaces in it. Should be the same result.

Solution 1 (general technique)

In the first row of Table 1::Website, put this formula:

=IFERROR(INDEX(Website, MATCH(A2, Table 2::Name, 0), 1), "")

Unfortunately, because Numbers thinks it's smarter than you are, it won't let me paste this in -- you'll apparently have to type it by hand. Then click and drag the yellow dot on the bottom of the cell all the way down to the bottom.

Let's unpack this from the inside out.

MATCH

Official documentation: http://help.apple.com/functions/mac/7.0/#/ffa59a83d3

MATCH takes the following arguments:

  1. Value to search for
  2. Range of values to search in
  3. Search "mode"

and returns the first position (1, 2, 3, etc.) in #2 that matches the value in #1. The documentation has more about argument #3, but 9 times out of 10 you will want to set it to 0,

INDEX

Official documentation: http://help.apple.com/functions/mac/7.0/#/ffa59b4edb

INDEX takes the following arguments:

  1. A range of cells to select from
  2. A row in that range to select
  3. A column in that range to select
  4. An area number to use in the selected cell

We want to select from Table 2::Website, so that's what goes in to #1. The result of the MATCH call above gives us the row number we want, so that goes into #2. There's only one column in that range, so we know we want to put 1 into 3. If #4 is omitted, it defaults to 1. Don't worry about this, just leave it out.

IFERROR

Official documentation:

IFERROR takes the following arguments:

  1. A value to check for errors
  2. A replacement value if there's an error

This one is self-explanatory. If INDEX fails, it will produce an error. Use this to replace those errors with blanks (or something else if you desire).

Solution 2 (shortcut)

You can simplify this query with a shortcut function:

=IFERROR(VLOOKUP(A2, Table 2::Name:Website, 3, 0), "")

What this does is "vertically looks up" A2 in the range Table 2::Name:Website and returns the value in column 3 that from the row it found. The 0 at the end specifies to look for an exact, rather than approximate, match. The IFERROR part is identical.

shadowtalker
  • 408