+ Reply to Thread
Results 1 to 5 of 5

Help with YTD sales data comparison from two years - need to match and compare

  1. #1
    Registered User
    Join Date
    11-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Help with YTD sales data comparison from two years - need to match and compare

    I have two sheets of data with sales figure totals and quantity shipped based on company names. I have one sheet for 2012 and one for 2013. I need to put these on one sheet and then compare the sales data. I have some companies in 2012 which are not listed in 2013 and some new ones in 2013 that were not in the 2012 sheet. I don't know how to merge these two sheets together into one matching up companies names that are identical and then get the +/- calculation for the sales comparison and the quantity shipped. The attached example (sheet3) is how I want the final sheet to look. HELP!! Thank you!!
    Attached Files Attached Files

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Help with YTD sales data comparison from two years - need to match and compare

    Im givin you new lay out of your table not far different, but more efficient I think...

    Please see the file

    Azumi
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Help with YTD sales data comparison from two years - need to match and compare

    Thank you very much! That is perfect and is a much better configuration! I appreciate your help!

  4. #4
    Registered User
    Join Date
    11-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Help with YTD sales data comparison from two years - need to match and compare

    How would I update the formulas to account for the following:

    Sheet1 has 698 records
    Sheet2 has 629 records
    I want sheet3 to start at the top row (not row 12)

    I tried to update the formula but wasn't able. Thank you!

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help with YTD sales data comparison from two years - need to match and compare

    i think yo are overcomplicating things
    simply copy col A from sheet 1 to sheet 3 and then copy col A from sheet 2 and paste below on sheet 3
    sort
    then use excels remove duplicates tool
    then you can just use vlookup
    A
    B
    C
    D
    E
    1
    company name 2012 sales qty shipped 2013 sales qty shipped
    2
    AA MOVING
    10000
    900
    2000
    50
    3
    ABC Site
    10000
    300
    4
    ACDC Music
    5000
    200
    20000
    800
    5
    ADAM'S Moving
    5000
    150
    6
    AFTER ALL CO
    15000
    1000
    7
    AGO INC.
    1000
    20
    7000
    180
    8
    ALL THAT CO.
    7000
    490



    B
    C
    D
    E
    1
    2012 sales qty shipped 2013 sales qty shipped
    2
    =IFERROR(VLOOKUP(A2,Sheet1!$A$2:$C$20,2,FALSE),"")
    =IFERROR(VLOOKUP(A2,Sheet1!$A$2:$C$20,3,FALSE),"")
    =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$C$20,2,FALSE),"")
    =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$C$20,3,FALSE),"")
    3
    =IFERROR(VLOOKUP(A3,Sheet1!$A$2:$C$20,2,FALSE),"") =IFERROR(VLOOKUP(A3,Sheet1!$A$2:$C$20,3,FALSE),"")
    =IFERROR(VLOOKUP(A3,Sheet2!$A$2:$C$20,2,FALSE),"")
    =IFERROR(VLOOKUP(A3,Sheet2!$A$2:$C$20,3,FALSE),"")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot table Sales comparison over years
    By Holymoly in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-25-2013, 11:21 AM
  2. Replies: 2
    Last Post: 10-16-2012, 11:40 AM
  3. Replies: 4
    Last Post: 08-28-2012, 01:31 AM
  4. Replies: 0
    Last Post: 05-15-2012, 04:29 AM
  5. Compare two years accounts data
    By Snwbrd1799 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-18-2008, 11:31 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1