How to compare differences between spreadsheets?

I've used two tables, but this could be done with one.

The formula shown below Existing Inventory is entered in B2 of that table, and filled down to the end of column B.

B2: IF(COUNTIF(central inventory::A,A2)<1,A2,"")

COUNTIF counts the occurrences, in column A of Central Inventory, of each number in column A.

If that count is less than 1, IF copies the number in column A to the same row of column B. IF the count is 1 or more, IF places a null string "" ) in that row of column B.

IF there are blank cells in column A of both tables, then the count for those rows of Existing will be greater than 0, and IF will attempt to copy the value in, say, A8 into B8. The enpty cell will be interpreted as containing the numerical value zero, and the result will be as in the last three cells of Existing::column B.

You can avoid these zeroes by wrapping the formula in a second IF statement that checks for content in column A and returns a null string if there is none. If there is a value in the cell, This IF calls the main one (shown in bold here, which acts as above.

B2: IF(LEN(A2)<1,"",IF(COUNTIF(central inventory::A,A2)<1,A2,""))

Transferring the selected numbers to Central Inventory:

The simplest way to do this is to add a temporary single column table to the sheet.

Select all cells of Existing inventory::B. Copy. Click on an empty space on the sheet (not in an exisitng table), then go to the Edit menu and choose Paste Formula Results. This strips the formulas from the copy, and pastes the last calculated value for each copy of the formula, placing them in a new single column table.

Sort the column Ascending to place the numbers in order,

select and copy the cells containing serial numbers,

then select the first empty cell at the bottom of the list in column A of Central Inventory, and paste.

Sort ascending on column A of Central Inventory to place the existing and new numbers into correct (alphabetical) order.

Delete the temporary single column table.

Regards,

Barry

IF what you are calling "spreadsheets" are actually "tables" within the same Spreadsheet Document, then a formula solution should get you closer to your goal. If they are tables in separate documents, an Applescript soluion might be possible.

In Numbers' vocabulary, "Document" and "Spreadsheet" are short forms for "Spreadsheet Document" and mean the whole document contained in a single file. That document may contain several tables, and the tables may be placed on different Sheets within the document.

This statement is NOT clear:

"I need to compare and sort which ones are currently in there and strip the ones there not out into their own column."

Can you clarify your description?

Refer to each of the tables by name.

Are you attempting to ADD items to one of the tables? Which one? What factors determine which items are to be added?

Are you attempting to remove items from one of the tables? which one? What factors determine which items are to be removed?

The version of Numbers you are using, and the version of OS X or macOS running on your computer may be significant in determining a solution as well.

OS information: Apple menu > About this Mac

Numers version: Numbers menu > About Numbers

Regards,

Barry

Or use a short formula that results in a TRUE or FALSE boolean, like this:

=COUNTIF(Central::A,A2)>0

Filter on B for FALSE or set Conditional Highlighting to color FALSE, as shown.

SG