+ Reply to Thread
Results 1 to 12 of 12

Count, calculate, and graph sales of unique items over time

  1. #1
    Registered User
    Join Date
    11-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    15

    Count, calculate, and graph sales of unique items over time

    My company has a catalog of ~6000 unique SKUs that we sell online. Currently we do not have a way to quickly determine how much a given item has sold month over month, and the rate of that change.

    I have an excel workbook full of our last year's worth of sales data. I have organized it by placing each month of data in its own worksheet. I would like to be able to create a search box that will allow us to enter in a product ID, and have excel then create a graph with each month's of sales for that particular item. Is that clear? How should I go about doing this?

    Thanks!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Count, calculate, and graph sales of unique items over time

    Hi seerauber,

    This sounds like a simple Pivot Table/Pivot Chart problem. You would filter by the SKU and the graph would pop up showing what you need. My concern is that you have your data in a TABLE that allows a Pivot to be produced from it.

    Here is a random video showing pivot charts. If you supply a sample of your data it is about a 30 second job to do what I think you want.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

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

    Re: Count, calculate, and graph sales of unique items over time

    The best way to be able to quickly create charts like this (IMO) is to use a Pivot Table.

    Seeing that you have broken your data up into months on separate worksheets, I hope with exactly the same layout, you can make use of the Pivot Table Multiple Consolidation Ranges ( Alt + N+ Y+2 on my computer). This feature can be accessed by putting it on the Quick Access Tool Bar. Go to Excel Options, Quick Access Toolbar, All Commands. Scroll down to PivotTable and PivotChart Wizard, select and add to the QAT. Once installed, click on the icon in the QAT and follow the directions for Multiple Consolidation Ranges and add the ranges one at a time. Follow the prompts and you should get a nice rendering of your data.

    If you had all the data on one worksheet, it is much easier to produce a Pivot Table and Pivot Chart making use of the grouping of dates to get monthly reports etc. Powerful tool worth the trouble learning to use.
    <---------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

  4. #4
    Registered User
    Join Date
    11-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: Count, calculate, and graph sales of unique items over time

    Thanks all, I think you have me on the right track. I have been able to link the data tables up and create a pivot table. I haven't yet been able to get the pivot table to display the information I need.

    If it helps, each table has a column corresponding to:


    Top Level Category
    Product ID
    UPC/SKU
    Description
    Quantity Sold

    What I really need to do is pull the Product ID field and corresponding Quantity Sold field from each table, and present the results as a line graph (X axis = Months; Y axis = Quantity Sold) So I'm probably also going to need a search function (so that we don't need to scroll through 6000+ items each time we want to get the info)

    Apologies if I'm asking help for really basic tasks. I didn't realize how inept I am at Excel! Thanks!

    Please let me know if I can provide any more information that will make my question clearer and/or easier to solve.

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

    Re: Count, calculate, and graph sales of unique items over time

    If you post a file with what you need, it would certainly be easier to envision your needs and to give you help.

  6. #6
    Registered User
    Join Date
    11-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: Count, calculate, and graph sales of unique items over time

    So here I have some sample data for a few months - I've included the Product ID and sales numbers for 100 sample items. Each table represents a month's worth of sales.

    I would like to take this data (for all 6000+ of our actual inventory) and be able to look at a given SKU(say 56425) and have excel draw a line graph (or chart of some type) showing me the sales of that SKU month over month. I'm thinking I'll need some way to search for a specific SKU (even if just by ctrl + f) as trying to scroll to the correct one will be too time consuming.

    Is that clear? Thanks!
    Attached Files Attached Files
    Last edited by seerauber; 11-25-2013 at 10:35 AM. Reason: typo

  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: Count, calculate, and graph sales of unique items over time

    I created a sample report on Sheet5. I also added a Date column. There was insufficient data to produce a line chart but when there is, you can right click in the chart and choose a different chart type. As you filter the data in the Pivot table, the chart is updated to show the filtered data.

    The column titles can be changed and the chart formatted to suit. If you don't like the way the pivot table is being formatted you can select the chart copy it and paste and then format the copy as you see fit.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: Count, calculate, and graph sales of unique items over time

    sample data with exemplar graph.xlsxI still don't seem to be getting it. I've reattached the file with a graph (labelled 56696) that demonstrates what I'm trying to be able to do on demand for every SKU. I am sure that solution is staring in the face, but for some reason I'm just not seeing it. Is there any way you can make it simpler for me? It's driving me crazy that this should be a trivially simple task and yet I can't seem to make it happen.

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

    Re: Count, calculate, and graph sales of unique items over time

    All of your values are TEXT and that will not calculate. All values must be actual numbers. Your whole workbook acts strangely and I can't get it to act normally.
    Last edited by newdoverman; 11-25-2013 at 06:54 PM.

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

    Re: Count, calculate, and graph sales of unique items over time

    This is how I expected your data to react. I recombined all the example data onto the Combined sheet. I then created a normal Pivot table and line chart.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: Count, calculate, and graph sales of unique items over time

    Thanks so much! I got it working with that, and now I feel that I understand the whole pivot-table process. Your advice was very helpful, and now I have a half-decent sales and inventory tool.

    I'd love to eventually figure out how to do this without having to combine all the data on to one worksheet as it's useful to me to have that separate. So far, using your combination trick is the only way I can get it to work. Otherwise the pivot table won't allow me to break all my columns into fields, instead collecting them under 'Column'.
    Last edited by seerauber; 11-25-2013 at 09:57 PM.

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

    Re: Count, calculate, and graph sales of unique items over time

    You're welcome. The consolidation of the sheets should have worked but it didn't for some reason.

+ 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. Count, calculate, and graph sales of unique items over time
    By seerauber in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2013, 07:53 AM
  2. Formula to calculate how many unique items
    By James404 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-06-2012, 12:28 PM
  3. Count Unique Items
    By e602043 in forum Excel General
    Replies: 4
    Last Post: 10-05-2011, 07:43 PM
  4. Counting items for differnt sales groups by length of time.
    By JonkerBonnkers in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-18-2008, 04:10 AM
  5. Count Unique Items
    By Karleajensar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-05-2008, 05:20 PM

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