+ Reply to Thread
Results 1 to 8 of 8

Combine sales data in pivot

  1. #1
    Registered User
    Join Date
    12-07-2022
    Location
    Hoorn
    MS-Off Ver
    2210 excel
    Posts
    22

    Combine sales data in pivot

    Hi all,

    I am currently working on an excel sheet to forecast sales of spareparts. For this, i am seeking to combine two datasets.
    Dataset 1 comprises a list of product compositions. Dataset 2 comprises a list of sales, both new sales and sales of spareparts.

    My goal: based on a combination of new sales and historical spareorders, forecast the amount of spareparts that i am expected to having to deliver in the upcoming 3 months.
    Not sure yet what would be the best way to do this, maybe by means of a pivot or by graphing the trends or something??

    Anyone got any suggestions on how to operationalize this?
    I included the sheet in this thread
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Combine sales data in pivot

    on Blad1 you have 2095 duplicates
    on Blad2 - 453,
    is this correct data?

  3. #3
    Registered User
    Join Date
    12-07-2022
    Location
    Hoorn
    MS-Off Ver
    2210 excel
    Posts
    22

    Re: Combine sales data in pivot

    Yeah that's correct! (and exactly the biggest obstacle i'm facing as well)

    Sheet 'blad 1' can be read as a bill of material.
    Column A (Article) lists the products as a whole. Column B (Article 1) then lists the compositions that go into the products. Column C (Article 2) in its turn indicates the components that go into the compositions.

    For example, article 02.012.000 is a bike. The bike is a combination of compositions labeled 02.012.001 - 02.012.306. It includes compositions for the brakes, frame and tires, for example. Finally, each composition is a combination of components (50.006.000 = cable; PT.020.010 = chain; etc.)

    Since we sell multiple variations of our bikes, which all need cables, component 50.006.000 can be found multiple times across the Bill of Materials.
    Moreover, in the sales sheet (blad 2), we distinguish new sales from spareorders, where new sales exclusively entails the bike as a whole and spareorders typically entail compositions or components.


    Hope that clarifies it a bit?

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Combine sales data in pivot

    honestly I don't understand why you need these duplicates

    eg
    Article Article 1 Article 2
    02.012.000 02.012.007 02.012P007
    02.012.000 02.012.007 02.012P007
    02.012.000 02.012.007 50.002.VER
    02.012.000 02.012.007 50.002.VER

    or
    ArticleNR Amount Date Origin
    02.053.000
    10
    26/01/2023
    PRD
    02.053.000
    10
    26/01/2023
    PRD
    02.053.000
    10
    26/01/2023
    PRD
    03.006.000
    6
    17/05/2021
    PRD
    03.006.000
    6
    17/05/2021
    PRD
    03.006.000
    6
    17/05/2021
    PRD


    btw. I don't see expected result in your file

  5. #5
    Registered User
    Join Date
    12-07-2022
    Location
    Hoorn
    MS-Off Ver
    2210 excel
    Posts
    22

    Re: Combine sales data in pivot

    Ok.

    Gotta admit that i'm confused by the duplicates in 'blad 1'. It is likely a result of updated compositions where the obsolete compositions have not been eliminated from the database.
    This would indicate a more serious error in the database that i will have to dive into because it is likely to have larger implications across the firm.... thanks for noticing

    For the purpose of the sheet, the duplicate rows in 'blad 1' will have to be eliminated from the sheet.

    The duplicates in 'blad 2' however are correct. We produce in batches, resulting in multiple orders of the same article/product and volume to be completed on the same day.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Combine sales data in pivot

    still don't know what you want to achieve (expected result ??? ) but maybe

    Blad1 without duplicates
    Blad2 with duplicates (however Pivot will remove duplicates probably)
    ArticleNR contains all parts from Blad1 (if any)

    edit:
    of course you can choose different columns in Pivot
    Attached Files Attached Files
    Last edited by sandy666; 01-23-2023 at 06:12 AM.

  7. #7
    Registered User
    Join Date
    12-07-2022
    Location
    Hoorn
    MS-Off Ver
    2210 excel
    Posts
    22

    Re: Combine sales data in pivot

    Hey Sandy,

    Thank you very much. This is exactly what i was looking for and i feel really stupid for having overlooked this since it is really straightforward...
    Guess I was a bit absent this morning.

    Appreciate the effort!

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Combine sales data in pivot

    my pleasure



    if the problem is solved, hit Add Reputation (bottom left corner next to the post that was helpful)
    and then mark the thread as SOLVED (top above your first post - Thread Tools)

+ 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 Site/Sales Data is in Multiple Columns
    By eNinjaInTraining in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-10-2017, 01:35 AM
  2. Average sales per week per sales representative in pivot table
    By R12345 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-15-2016, 07:16 AM
  3. Pivot table with customer sales data, into an individual report with specific rows
    By fireforge112 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2014, 04:36 PM
  4. Useform which search the data of Primary sales and i can enter secondary sales data.
    By bipin_04 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-31-2013, 11:29 AM
  5. Replies: 4
    Last Post: 03-27-2013, 05:56 PM
  6. Combine and Total sales data into new sheet
    By kelemvor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2011, 03:16 PM
  7. Analysing sales data -pivot table novice
    By plainlazy84 in forum Excel General
    Replies: 3
    Last Post: 07-09-2008, 05:34 AM

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