Skip navigation

Advanced Numbers:  Trying to do an If/Then formula

3106 Views 4 Replies Latest reply: Mar 22, 2013 10:28 AM by Barry RSS
Matmaven Calculating status...
Currently Being Moderated
Mar 21, 2013 6:02 AM

I have two separate spreadsheets dowloaded from Quickbooks - one is 2010 and 2011 sales and the second is 2011 and 2012 sales.  I'd like to merge so that I have just one spreadsheet with all 3 years.  I can't just copy and paste, though, because the customers aren't necessarily the same from year to year, unless I do it be individual row.  I thought there was an If/Then formula I used to use in Excel...If this customer from 2012 is in this column from 2010-11 sheet, put the sales in this new column (2012) on the 2010-11 sheet.  Anyone know an easy way to do this?

MacBook Pro (13-inch Late 2011), OS X Mountain Lion (10.8.3)
  • Jerrold Green1 Level 7 Level 7 (28,120 points)
    Currently Being Moderated
    Mar 21, 2013 8:14 AM (in response to Matmaven)

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

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Mar 22, 2013 12:53 AM (in response to Matmaven)

    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.

    Picture 16.png

    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Mar 22, 2013 10:28 AM (in response to Matmaven)

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.