align cells with common values

Hi cnickflight,

Easily done by transferring the data to a second table.

Table 1 contains the original data. I've reduced the fruit names to three letter codes to reduce typing, but this doesn't change the process.

Column A of Table 2 contains a formula that copies existing data from column A of Table 1, and inserts null strings where there is no data in Table 1.

The formula is entered in cell A2, and filled down.

Table 2::A2: =IF(ROW()−1>COUNTA(Table 1::A),"",OFFSET(Table 1::$A$1,ROW()−1,0))

Fill down to the end of column A.

Columns B and C contain a VLOOKUP formula. The formula is the same in both columns, except for the number indicating which column of the lookup table the returned value is to come from. The 'testing' version of the formula in B2 is shown. When filled down, this formula should copy the fruit names where they that are in both lists on Table 1, in the row that they appear in column A, and display an error triangle in rows where there is no matching name in column B for the one in column A (and in the rows where column A is empty). The error message for each of these should be "VLOOKUP couldn’t find the requested value."

Table 2::B2 (testing version): =VLOOKUP($A,Table 1::$B:$C,1,FALSE)

(FALSE will display in the formula editor as "exact match")

If behaviour is as described, add IFERROR before VLOOKUP, and ,"") after the closing parenthesis to match the 'final version'. (See "Why two versions? below.)

Table 2::B2 (final version): =IFERROR(VLOOKUP($A,Table 1::$B:$C,1,FALSE),"")

Table 2::C2 (final version): =IFERROR(VLOOKUP($A,Table 1::$B:$C,2,FALSE),"")

Fill both down to the end of their respective columns. Results should now be as shown above.

Note: Why two versions?

IFERROR is great for use as an error trap, but it acts on ALL errors, not just the one you are intending to trap. Best practice is to add this trap only after you are certain the formula is not generating any error messages other than the one you are expecting and using In this case we know VLOOKUP, which has been instructed to find an "exact match" will throw an error when it can't find one. IFERROR catches that error, and places a null string in the cell, making it appear empty.

Regards,

Barry

Here are copies of the same tables, with numbers substituted for the text entries in the previous example.

Formulas are unchanged from the three in my previous post.

What results are you getting? What error messages? Please post a copy of the formula(s) in cell(s) showing error triangles, and a copy of the error message(s) shown when you click on the triangles.

Regards,

Barry

Hmmm... Puzzling.

I'd like to take a look at the data in Table 1. Do this please:

• Open a new message here.
• In the Numbers document, click on cell A1 of Table 1 to select it.
• Press and hold the shift key as you click on cell C9 of the same table.
• With that block of cells selected, press command-C or go Edit>Copy
• Switch back to Safari
• Click in the body of your new message.
• Paste (command-V or Edit>Paste.
  

You should then see a table in the message with the same content as in that section of the Numbers table.

Repeat with cells A1 - A9 pf Table 2.

Questions:

How were the numbers entered into each of these columns:

column A of Table 1?

column B of Table 1?

column A of Table 2?

If the last one was done using a formula, a copy of the formula would also be useful.

Barry