Merging/comparing multiple sheets without duplicates and grabbing highest values?

Hi Circleof05ths,

This screen shot shows the three tables on one Sheet (Tab). The formulas will work between different sheets within the one document.

Formulas in Table 3 ( and fill down). The IFERROR function inserts zero if there is no match.

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

C2 IFERROR(INDEX(Table 2::B,MATCH($A2,Table 2::A,0)),0)

D2 MAX(B2,C2)

When all is working, select and copy column D, then Menu > Edit > Paste Formula Results.

Then you can delete columns B and C in Table 3.

Regards,

Ian.

Hi Circ'

"something is wrong with the formula"

True, and the error message tells you what is wrong with it:

“This formula can’t reference it’s own cell, or depend on another formula that references this cell.”

At first glance, it appears that a self reference error referencing the cell containing the formula may override the IFERROR instruction to place a zero in the cell.

MATCH says: 'Get the value from cell A2' (of 'this table')

For the copy of the formula that is in cell A2 of this table, that is a self-reference, and the cause of the error.

The value in cell A2 is created by the formula in A2, but the formula depends on that value to create that value, which Numbers won't tolerate.

"The other columns return zero when I put the formulas in."

As they should. MATCH in these cells depends on the value in A2 as a value to search for in column A of Table 1. As there is no value in A2 of 'this table', the formula is apparently finding the empty cells at the bottom of Column A of Table 1, and returning a 0 numeric value of the content of that cell.

In Ian's example, the formula you used is in the selected cell (B2 of Table 3) shown in his screen shot, and references cell A2 of that table to get the value to search for. The values in column A of Table 3 appear to have been entered from the keyboard, as there's no formula shown for creating them.

Given the number of distinct fruit names involved, I suspect Ian copies the list from one of Table 1 or Table 2, then added the distinct names from Table 2.

Bottom line:

• Delete the formula in A2 of your copy of Table 3 (and any copies of that formula in the rst of the cells in column A of that table. (Leave the copies in other columns in place.)
• Copy the Column A list from either Table 1 or Table 2, select A2 of Table 3 and Paste.
• Add the names from column A of Table 2 that are NOT present on Table 1.

Your table should then be working.

Regards,

Barry

You don't need to struggle with formulas to do this. You can do it with a few clicks and copy-pastes.

Copy-paste the values in one table into the bottom of the other so all the data is in one table. Don't worry about duplicates.

Right-click in the fruit column of the table with all the data and choose Add Category for ...:

Click the 'gear' icon in the column with the quantities and choose Maximum.

Collapse the Peer Groups:

Select the visible cells, command-c to copy to clipboard, and in another existing table Edit > Paste and Match Style.

Giving you something like this:

Delete any extra columns in the middle.

This all should take you less than a minute, and you're done

SG

Circleof05ths wrote:

Thanks for the help. I’ll try this out. I wanted to do this as a formula so I could set up a template for myself and have it automated, but this is a good back up.

Sometimes trying to over-automate with formulas doesn't make sense. If you can do something in a few seconds with a few clicks and copy-pastes, using an interface Apple designed to make tasks like this easier than with formulas, why go through lot of pain to set up formulas to do the same thing? That only makes sense if you have to constantly update every few seconds, which doesn't seem to be the case here.

SG

In the Data Format dropdown in the panel at the right have you tried setting the number of decimal places following the decimal point (of course after selecting the body cells in the column you want to format)?

SG

Well, good luck, with whatever those "many, many" reasons are.😀

SG