Advanced Numbers: Trying to do an If/Then formula

This is all rather hypothetical. Could you post what the old and combined tables would look like?

Jerry

Assumed:

Both tables have one header row.

2010 sales on Table 1, 2012 sales on table 2.

Customer names in column A, Sales in columns B and C (both tables)

Ech customer name appears no more than once on each table.

Transfer formulas will copy values from columns A, B and C of Table 2 to columns D, E and F of Table 1

This gives a visual check of matching names in columns A and D of table 1 after the transfer. Column D will be deleted after you are satisfied that the transfer is correct.

You will need to determine how to resolve 2011 sales which may be listed in one, neither or both of columns C and E.

Transfer formulas will be replaced with their results by COPYing the cells in those three columns, then using PASTE VALUES to replace the formulas with the calculated values.

A mark will be placed in Column D of Table 2 to indicate which rows have had their values transferred, and permit easy sorting AFTER using COPPY/PASTE VALUES as above..

Adjust the columns to fit your tables and try this on a COPY of your tables.

Formulas

Table 1:

D2: =IFERROR(VLOOKUP($A,Table 2 :: $A:$C,COLUMN()-3,0),"")

Fill right into E2 and F2, then fill all three down to the bottom row of the table.

Table 2:

D2: =IFERROR(IF(MATCH(A2,Table 1 :: $A,0)>0,"TX",""),"")

Fill down to end of column.

Check that names match in each row of columns A and D.

When satisfied with the transfer, select columns D, E, and F of Table 1, Copy, then go Edit > Paste Values.

Select column D of Table 2. Copy, then go Edit > Paste Values.

Sort Table 2 on column D (the column containing only TX and blank cells).

Select columns A, B and C of the rows that do NOT have TX in column D. Copy.

Click on any cell in Table 1, then use the Row control Handle to add a row to the bottom of Table 1.

Click on the cell in column D of that new row, and Paste.

Select the customer names in the new rows of column D and Copy.

Click on the first new row cell in column A and Paste.

To combine the amounts in columns C and E (2011 sales from each of the two tables), Enter this formula in D2 (replacing the Customer name there):

=C+E

Fill down to the bottom of the column.

With all of column D selected, Copy, then go Edit > Paste values.

Label this column "2011", then selete columns E and C.

Done.

REgards,

Barry

Hi M',

Most times one or more screen shots showing the parts of the table(s) involved, plus the formulas you've used, copied from the table and pasted into your message, will provide enough information to find any errors. If you're getting error messages, include those as well.

To take a screen shot:

Place the mouse pointer at one corner of the area you want to capture.

Press shift-command-4 (The mouse pointer will change to a crosshair image)

Press and hold the (left) moouse button as you drag to the opposite corner of the area you want to capture.

Release the mouse button.

The image will be saved to your desktop with the name Picture n OR ScreenShot date and time.

To add the screen shot (or other image) to your post:

Place the insertion point where you want the image to appear.

Click the camera icon in the tools above the message space.

Click Choose file.

Select the image file from the list (you may need to navigate to the Desktop)

Click Choose.

Click Insert image.

If there's a need to see the actual file, email works. If there's not an email link at the top or bottom of this message, click my name (above the message) to get it from my profile.

Regards,

Barry