Merge & Match Numbers into 1 spreadsheet

in the top table, add two columns.

select cell D2 then type (or copy and paste from here) the formula:

=vlookup(A2, Table 1::A:C, 2, 0)

shorthand for this is:

D2=iferror(vlookup(A2, Table 1::A:C, 2, 0), "")

E2=iferror(vlookup(A2, Table 1::A:C, 3 0), "")

select cell D2 and E2, copy

select cells E2 thru the end of column E, paste

I have a typos in the formula:

use the following:

shorthand for this is:

D2=iferror(vlookup(A2, Table 1::A:C, 2, 0), "")

E2=iferror(vlookup(A2, Table 1::A:C, 3 0), "")

select cell D2 and E2, copy

select cells E2 thru the end of column E, paste

"Another member asked me to clarify this"

If memory serves, I was "another member." You've made it a bit difficult to tell by starting a new discussion rather than using the "Reply" button to continue discussing the same issue in the same thread.

"Basically, I what I need is the FID number on one sheet to line up with the FID on other sheet with spaces in between the ones that don't match, but with all data combined onto one spreadsheet."

Let's start by naming that "one spreadsheet" (actually, that one table) "combined"

If I'm reading your need statement correctly

• You need "combined" to include "all data" (from both source tables).
• Each fid number listed (on either source table) is to have a line on "combined"
• Each line on "combined" is to contain "all data" connected with the fid number on that line.
• If the fid number is included on Table 1 AND on Table 1-1, that line of "combined" will contain the data from Table 1 associated with that fid, AND the data from Table 1-1 associated with that fid.
• If the fid number is included only Table 1, that line of "combined" will contain the data from Table 1 associated with that fid. The cells in columns for data from Table 1-1 will be 'empty.'
• If the fid number is included only Table 1-1, that line of "combined" will contain the data from Table 1-1 associated with that fid. The cells in columns for data from Table 1 will be 'empty.'

Here's an example based on your data above. I've replaced the 'filenames' data with constructed filenames to avoid having to type them in, and constructed the filepaths based on the new filenames data, but have used your sample data in the other columns of the two source tables.

Note that I have reversed the order of the fid and nid columns in Table 1-1. This is for two reasons:

--to make the fid placement consistent between the two tables

--to place the fid in the leftmost column, as required for use with the VLOOKUP function.

Switching the positions of the first to columns is a simple drag and drop operation:

Click on a cell in column A of Table 1-1 to make that the active table and show to row and column reference tabs above and to the right of the table.

Click on the column reference tab for column A to select that column. Release the mouse button.

Click again on the reference tab, placing the mouse pointer just to the left of the "A". Hold the mouse button down and drag the tab (and the whole column) down and to the right. When a thicker blue line appears between the fid column and the Field photo list column, release the mouse button to drop the nid column back into the table at that position.

(You may want to practise this a few times on a new table before trying it on your data table.)

Two new tables:

Aux is a two column table, used to collect the full lids of fid numbers from the two source tables, and to eliminate duplicates by converting the copy of the duplicate number from Table 1 to a very large number (9999999). See notes below regarding this table.

combined is the result table collecting all of the data recorded on the two source tables.

combined:

This table used three formulas, entered in A2, B2 and D2, then filled into columns A, B & C, and D & E.

Column Header labels are entered into ther respective cells in row 1.

A2: =IF(SMALL(aux :: A:B,ROW()-1)<MAX(aux :: A:B),SMALL(aux :: A:B,ROW()-1),"")

Filled down to the end of column A.

SMALL returns the nth smallest number from a collection of numbers. "nth" is determined by ROW()-1.

IF permits the returned value to be recalculated and returned to the cell if the result is smaller than the maximum (9999999) value in the collection, otherwise the formula returns a null string ( "" ), giving the appearance of an empty cell.

B2 (core) =VLOOKUP($A2,'Table 1-1' :: $A:$C,COLUMN(),FALSE)

B2: =IFERROR(VLOOKUP($A2,'Table 1-1' :: $A:$C,COLUMN(),FALSE),"")

Filled right to C2, both filled down to the end of their respective columns.

The core formula (shown first) is a straight VLOOKUP which gets an fid value from column A of this table and looks for it in the leftmost column of the three column lookup table, Table 1-1::$A$C. If it finds that value, it returns the value from 'this column' ( COLUMN() ) of the table. FALSE here means VLOOKUP will accept only an exact match. If it does not find an exact match, VLOOKUP throws an error.

In the full formula (shown below the core version), an error trap has been added to catch the error and return a null string to the cell.

D2: =IFERROR(VLOOKUP($A2,Table 1 :: $A:$C,COLUMN()-2,FALSE),"")

Filled right to E2, both filled down to the end of their respective columns.

This is the same formula as in B2 and C2, edited to search and return values from Table 1, and to get the returned values from columns two to the left ( COLUMN()-2 ) of the column containing the formula.

aux

As noted above, this auxiliary table gathers and filters the fid numbers to provide a single copy of each fid, and several very large numbers that will not appear in the list. Using Union-Range in Numbers 3 (not available in Numbers '09, which I'm using) may provide a means of collecting this set of fids without the use of an auxiliary table.

aux contains two formulas.

A2: =IF(LEN('Table 1-1' :: A2)<1,9999999,'Table 1-1' :: A2)

Filled down to end of column.

LEN() measures the length, in characters, of the entry in this row of column A of Table 1-1.

IF that length is less than 1 character (ie. if the cell is empty), the formula returns a VLN, larger than the maximum fid number, otherwise, the formula returns the value from that cell.

B2: =IF(OR(LEN(Table 1 :: A2)<1,COUNTIF($A,Table 1::A2)>0),9999999,Table 1 :: A2)

Filled down to end of column.

This is the same formula as in A2, with one added condition.

An empty cell in this row of column A of table 1 OR a duplicate in this row of column A of any value in column A of this table will cause IF to return the VLN. If the target cell is not empty and does not contain a value already in the list IF returns the value from the target cell.

NOT HANDLED:

If aux is longer (in rows) than either of the source tables, the A2 reference will attempt to read cells beyond the end of the table it references, and will throw an error. As all cells in these columns are referenced by the formula transferring the fid numbers to "combined",column A of "combined" will then fill with error triangles, and the rest of the cells in "combined" will go 'blank.'

IF filling the combined table is a one-time operation, after which data entry will be made directly to that table, this should not be a major issue. If future entries will be made to the two source tables, and transferred to combined, then you will want to place an error trap onto these two formulas, allowing the length of the aux table to be more than the length of either source table, and giving room for those tables to expand.

Error trap with VLN return: =IFERROR(formula,9999999)

Replace formula with the existing formula before filling it down the column.

Regards,

Barry

HI Tara,

From your reply to Wayne:

From Wayne's initial post:

=vlookup(A2, Table 1::A:C, 2, 0)

There's no error in the formula, other than its getting the search value from the wrong column of Table 1-1 ('this table')

The issue here is that Column A of 'this table' contains the nid numbers, not the fid numbers. The highlighted cell, A2, contains a 5.

The zero in VLOOKUP tells VLOOKUP to accept only an exact match. There's no 5 in column A of Table 1, so VLOOKUP throws an errorwith the 'can't find it' message shown in your post.

Wayne added an error trap to the formula just below the one with instructions to copy and paste:

D2=iferror(vlookup(A2, Table 1::A:C, 2, 0), "")

This will remove the error triangle, and replace it with a null string (the cell will appear empty), but the error message is useful, and shouldn't be trapped until he core formula is producing the Find results you want.

Changing A2 to B2 in the formula above will rid you of the error flag in that cell (because there is a matching 2 in column A of Table 1), but the end result will not match my reading of what you need.

I think Wayne' interpretation of the question differs from mine. Please check the tables in my post above to see if "combined" is closer to what you need.

Regards,

Barry

Hi Wayne,

You wrote:

"I have a typos in the formula:"

??

"old" from your initial post, "new" from your second post. A2 reference is to the nid column, not fid, but I don't see a typo in the formula(s).

old D2=iferror(vlookup(A2, Table 1::A:C, 2, 0), "")

newD2=iferror(vlookup(A2, Table 1::A:C, 2, 0), "")

old E2=iferror(vlookup(A2, Table 1::A:C, 3 0), "")

newE2=iferror(vlookup(A2, Table 1::A:C, 3 0), "")

Regards,

Barry

I was referring to:

Wayne added an error trap to the formula just below the one with instructions to copy and paste:

I posted a formula without the trap. The later changed it-- which you noted.

If I understand what you are trying to do correctly, the way I would approach the problem is to set up a third table with a list of distinct fids (i.e. a list of all fits without duplicates), and then look up the corresponding values from the two source tables, something like this:

There are various ways to get a list of distinct fids. One quick way is to use the script below (no AppleScript knowledge is needed to use it.) Simply copy-paste it into Script Editor (in Applications > Utilities), adjust the table names in the script if they are different from your screenshot, click the Script Editor 'run' button, click once in cell A2 of the 'Combined' table, and command-v to paste the values into the column.

Then enter this formula in B2, and fill it down the column:

=IFERROR(INDEX('Table 1-1'::$A,MATCH($A2,'Table 1-1'::B,0)),"")

In C2, filled down:

=IFERROR(INDEX('Table 1-1'::C,MATCH($A2,'Table 1-1'::$B,0)),"")

In D2, filled down:

=IFERROR(INDEX(Table 1::B,MATCH($A2,Table 1::$A,0)),"")

In E2, filled down:

=IFERROR(INDEX(Table 1::C,MATCH($A2,Table 1::$A,0)),"")

Then Sort Ascending on column A if you want the fids in order.

SG

property nameFirstTable : "Table 1-1"

property nameScndTable : "Table 1"

property fidColFirstTable : 2

property fidColScndTable : 1

tell application "Numbers"

tell front document to tell sheet 1

set fidLst1 to tablenameFirstTable'scolumnfidColFirstTable'scells'sformatted value

set fidLst2 to tablenameScndTable'scolumnfidColScndTable'scells'sformatted value

end tell

end tell

set fidTmpLst to fidLst1 & fidLst2

set distinctFid to {}

repeat with i in fidTmpLst's items

if i is not in distinctFid and ¬

i as string is not "fid" and ¬

i as string is not "missing value" then ¬

copy i's contents to distinctFid's end

end repeat

set text item delimiters to return

set distinctStr to distinctFid as string

set text item delimiters to ""

set the clipboard todistinctStr

"I was referring to…"

Thought that might have been it. 😉

Regards,

Barry

HI SG,

Similar to my approach using formulas an an auxiliary table in place of script and the clipboard. One difference I notice (which may or may not be of importance to the OP) is that my use of SMALL preserves the ascending order of fids (apparent in both source tables) in the combined table that your script does not. I suspect implementing that feature would present only a minor challenge, and am surprised you didn't include it.

Regards,

Barry

Barry wrote:

HI SG,

SMALL preserves the ascending order of fids (apparent in both source tables) in the combined table that your script does not. I suspect implementing that feature would present only a minor challenge, and am surprised you didn't include it.

Hi Barry,

To avoid confusing myself I like to keep scripts small and focussed on things that save work and minimize complication. Using a script to generate a list of distinct values can be easier (especially after the script is written) than using formulas and extra columns, etc. Sorting a list of fids, while not hard to do in a script, does introduce complexity. Sorting is so easy in Numbers (just click the column letter and choose Sort Ascending) that I thought best to let Numbers do that part. The same goes for the lookups. They can be done via a script, but it's easier to do them with a handful of formulas in Numbers.

SG