+ Reply to Thread
Results 1 to 7 of 7

Want to show change in sales between 2 slightly different lists of products

  1. #1
    Registered User
    Join Date
    09-30-2011
    Location
    Brunswick, ME
    MS-Off Ver
    Excel 2007
    Posts
    10

    Want to show change in sales between 2 slightly different lists of products

    Sorry in advance, I'm an excel newbie and I'm asking on behalf of the boss since I've had
    good luck with this forum in the past. Hope I can phrase the question properly.

    We have a list of products and their sales figures from 2014.
    We have a similar list of products from 2015, but a few items were added and removed.

    How can I compare sales figures between the 2 years,
    but only for products that exist in both years...
    ignoring products that were added/dropped?

    The results of this comparison could be output to a new column...
    Or maybe just highlighted in the 2015 column, e.g. red = sales decreased, green = sales increased.
    Whatever's easiest.

    Note that the same product names appear under multiple distributors,
    so e.g. Ajax Turner shows Alfredo Roca in one year, but not the other.
    But Aleksey's has Alfredo Roca also, and it appears both years.

    The screenshot may help make it clearer I hope.
    Thanks very much in advance!

    excel_question.jpg

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Want to show change in sales between 2 slightly different lists of products

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Want to show change in sales between 2 slightly different lists of products

    Do you only want to compare products within the different suppliers?

  4. #4
    Registered User
    Join Date
    09-30-2011
    Location
    Brunswick, ME
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Want to show change in sales between 2 slightly different lists of products

    Hey, thanks for the replies. I did the screenshot (with relevant parts blurred) because I thought the boss might balk at putting up actual sales data. But I checked and it should be ok to do a limited example with 2 suppliers.

    Hoya: yes, compare products only within the supplier.

    Attached is the sample workbook with just 2 suppliers. I think the columns were created via pivot table.
    The source data is on sheet 2.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-30-2011
    Location
    Brunswick, ME
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Want to show change in sales between 2 slightly different lists of products

    Bumping, anyone have any ideas?

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Want to show change in sales between 2 slightly different lists of products

    In D4 enter this formula and fill down to D31 which will identify all products in the 2014 list that are in the 2015 list for Ajax.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In H2 Enter this formula in H2 and fill down which will identify all products in the 2015 list that are in the 2014 list for Ajax.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Where you want the total sales for the products in common enter this formula and format as currency:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Repeat for Aleksey's as follows:
    Enter in D34 and fill down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter in H34 and fill down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Where you want the answer enter:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Repeat for each supplier.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Want to show change in sales between 2 slightly different lists of products

    Here is a different arrangement of your main data and a Pivot Table developed from the new arrangement.
    The pivot table has a filter containing 1 and 2. If you want the products that show in both lists select 2. If you would like to see those that only appeared once from the two lists then select 1. If you want to see everything select ALL.
    Attached Files Attached Files
    Last edited by newdoverman; 09-28-2015 at 04:20 PM.

+ 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. [SOLVED] Formula to List of Product Groups That Have The Most Products With Zero Sales
    By chaddug in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2015, 06:12 AM
  2. [SOLVED] Help Required: Stock lists - Calculating combinations of products
    By MaD2ko0l in forum Excel General
    Replies: 12
    Last Post: 05-12-2013, 10:47 AM
  3. How to show the relationship between sales and cost of sales
    By chawee in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-01-2012, 11:56 PM
  4. Calculate Intersection Point of 2 Products Sales
    By ClikClak in forum Excel General
    Replies: 5
    Last Post: 05-12-2010, 04:21 PM
  5. merging slightly different lists together
    By fingraphics in forum Excel General
    Replies: 1
    Last Post: 03-26-2009, 02:29 PM
  6. Replies: 1
    Last Post: 06-16-2008, 05:42 PM
  7. Replies: 2
    Last Post: 01-29-2005, 07:06 PM

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