+ Reply to Thread
Results 1 to 8 of 8

Moving big amounts of sales statistics to one row per product

  1. #1
    Registered User
    Join Date
    05-19-2019
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    5

    Moving big amounts of sales statistics to one row per product

    Hello. Can someone please help me.

    I have been struggling with this for days! In the screenshot you will see sales statistics of several products (A:D) from different branches (E) in specific years (F). In the end the goal is to get the sales statistics per month (G:AD) in one row (see the red squares). Then duplicated rows of a products will be deleted so that i have one unique row per product per store with sale statistics for May 2018 to April 2019.

    The problem is shifting sales to one row takes a lot of time (14500 rows!) by hand.

    I tried conditional formatting and formulas but I am stuck.

    Some products have sold in every year (2017,2018,2019 blue highlight), some in two years (2017,2018 or 2018,2019, etc.green highlight) and some only in one of the years (yellow highlight).

    Any ideas on solving this problem? It would be really appreciated!

    Regards

    Bitter

    Problem-01.jpg

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,757

    Re: Moving big amounts of sales statistics to one row per product

    No-one is going to want to recreate that...

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually).

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    05-19-2019
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    5

    Re: Moving big amounts of sales statistics to one row per product

    Hi Glen!

    Thank you vermy much for the reply. I am new at forums, so please be patient

    I have hopefully followed you're instructions correctly.

    On the attatched file there are a few more lines than 20, but I thought this will give a real perspective on the whole file of 14158 lines of data.

    The data are truly representative of the real file. Merged cells deleted. I have put the Before data in that sheet and the desired goal in the After sheet.

    Hope this helps. Thank you again. Much appreciated.
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    19,162

    Re: Moving big amounts of sales statistics to one row per product

    I created a Pivot Table using your data to replicate your needs. See attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-19-2019
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    5

    Re: Moving big amounts of sales statistics to one row per product

    Glenn

    You are my newest hero! Everything worked out. 16000 lines sorted and done!

    I totally did not think Pivot Table would be able to cope with the way the data were set up.

    When you visit SA we should meet for a beer and biltong!

    Thank you very much

  6. #6
    Registered User
    Join Date
    05-19-2019
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    5

    Re: Moving big amounts of sales statistics to one row per product

    Thank you Alan

    It worked! You guys are really helpful. 16 000 lines of data sorted. Please visit SA with Glenn for my treat for a beer and biltong!

  7. #7
    Registered User
    Join Date
    05-19-2019
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    5

    Re: Moving big amounts of sales statistics to one row per product

    Thank you everybody. Thread SOLVED

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,448

    Re: Moving big amounts of sales statistics to one row per product

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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. Sales per product per salesperson
    By rcornell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-03-2019, 07:57 PM
  2. Replies: 4
    Last Post: 02-01-2017, 08:13 PM
  3. Help with Formula to Sum Product Sales based on Sales Channel and Product
    By Casehype in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-28-2015, 07:20 PM
  4. Product Sales to Month Sales
    By pelamis22 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-23-2013, 06:13 PM
  5. chart for product sales
    By metalgearslug in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 06-26-2010, 09:39 PM
  6. [SOLVED] Percentage of amounts and profits of individual sales
    By lulubelleshell in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-12-2005, 03:05 PM
  7. Replies: 0
    Last Post: 08-28-2005, 11:07 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