looking up and copying adjacent data

Hi AJ,

I am wondering if just sorting each Table by the first Column will bring them both into line. Then Copy from Table 2 and Paste into Table 1.

I am not quite sure what you mean by adding a Row from the second Table into the first Table. If each has a Column of key numbers, perhaps you mean to add Columns, not Rows?

Screen shots of small parts of each Table will help. Remove all personal data before taking screen shots.

Regards,

Ian.

AJ,

Along the lines of Ian's suggestion, but in a different order...

First, Duplicate your document so you have a backup if things go off course.

Then...

I would Add a Row at the bottom of the first Table so you have a blank row to Paste into. Then Copy the entire Body of the Second Table and Paste onto the Column A cell of that blank row you just created in the First Table. (Let's call these Tables, not Sheets, as that is the Numbers notation.) You don't have to worry about providing room for the second table's data because the Paste operation automatically expands that table to fit.

Now you have one inclusive database (the first Table), but perhaps with some duplicated, and not necessarily identical, records (rows).

Next, sort on the first column so that duplicated records will lie adjacent to one another. Depending on the size of your database, a quick visual scan may be sufficient to find the duplicate identifiers. If that's not the case, you can create a duplicate flagger column. In this new column, write the expression:

=IF(COUNT(A, A)>0), "DUP", "") and Fill or Paste the expression to all the body rows in that column. We assume that the identifier is still in Column A.

Now you surely can quickly find and deal with the duplicate entries by looking for rows with the text "DUP" in the new column.

Jerry

Seeing your screen shot, I'd recommend that you use a lookup function. This assumes that there are no conflicts (only one id per missing value).

Jerry

Hi AJ,

From your screen shot it appears that you have two blocks of data on one Table. I am not really sure, because the Row numbers are partly obscured.

Try this.

Save your work if the document is open.

Make a copy of the document to try this method.

Here I have reproduced some of your screen shot with imaginary values:

In the first block of data, there is a Code (99999) that does not appear in the second block of data. Similarly, Code 10007 in the second block does not appear in the first block. I did this to play Devil's Advocate.

Click on the Reorganize button in the tool Bar and Sort the entire table by Column A (Code):

That will show that Codes 10007 and 99999 are on their lonesome, but the other codes pair up. If you really do have "millions" of Codes, you will need some sophisticated formulas to compare consecutive Rows and find the lonesome Codes.

I think there is another way. Go back to your original document and make another copy to play with.

Here it is, and I have split the two blocks of data into two real Tables. That is the Numbers way: separate Tables for discrete blocks of data:

The formula in Cell E2 of Table 1 is:

=IFERROR(VLOOKUP($A2,Table 2::$A$2:$C$4,2,0),"Not Found")

Fill Down in Column E.

The formula in Cell F2 of Table 1 is:

=IFERROR(VLOOKUP($A2,Table 2::$A$2:$C$4,3,0),"Not Found"

And Fill Down in Column F.

Those formulas will grab the values in Table 2 and place them beside the corresponding Code in Table 1. As there are two Size columns in the data, I have called them Size ml and Size X.

"Not Found" shows that Code 99999 does not occur in Table 2. However, this method will not tell you that Code 10007 does not occur in Table 1.

Regards,

Ian.

Hi AJ,

Thanks for the Greenie and feedback.

Oops, my second formula is missing the final bracket.

=IFERROR(VLOOKUP($A2,Table 2::$A$2:$C$4,3,0),"Not Found" should be:

=IFERROR(VLOOKUP($A2,Table 2::$A$2:$C$4,3,0),"Not Found")

Regards,

Ian.