+ Reply to Thread
Results 1 to 5 of 5

Pivot Table Site/Sales Data is in Multiple Columns

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    64

    Question Pivot Table Site/Sales Data is in Multiple Columns

    I need to evaluate shipment orders that allocate promotional item sales across shipment sites. Since multiple promotions will be analyzed (2 are provided in attached), a pivot table filtered by promotion seems most logical (and quickest). I need my table to list “Shipment Site”, “Sales”, and “Promotion Applied”. However, the site and sales data I need span multiple columns since multiple sites can be used to complete an order (i.e. an order of 5 may pull 2 items from one site, 3 from another). Our software’s output (yellow highlighted column in attached) lists sites and sales joined by a colon. I separated these into new columns (data > text to column) (I did not look into trying to change text to rows since I would have to copy the remaining data down each time and there are thousands of records). I can’t figure out how to best consolidate my shipment site and sales data info. Since I’ve been playing with this for some time now, any help or insight would be absolutely super!!

    ((disclaimer: data presented not actual data))
    Attached Files Attached Files

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    953

    Re: Pivot Table Site/Sales Data is in Multiple Columns

    Hi eNinjaInTraining,

    Your data formatting doesn't make it easy to do what you need.

    You could condense your Columns for 'Shipment Site', 'Qty Shipped' & 'Sales' into 3 Columns instead of 9 by creating a new Column called 'Shipment Site #' with the values of 1, 2 or 3. - I'll refer to these Columns as your 'Sales per Order' data.

    This will cause some duplication of the other Columns so you need to keep that in mind.

    See if the attached helps your issue.

    Cheers
    Attached Files Attached Files
    Remember you are unique, like everyone else

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Pivot Table Site/Sales Data is in Multiple Columns

    Data was changed using Power Query
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    64

    Re: Pivot Table Site/Sales Data is in Multiple Columns

    Noboffinme, did you copy and paste the rows individually to get the site numbering? While your format certainly makes for a cleaner way to run a pivot, I worry a little about the time to do this. It looks to me like I would have to analyze each order individually, and some orders are partially shipped out of 15+ sites. If I'm not seeing things right, please let me know - I had a long shift today, so may be unknowingly fried haha.

    Stasinek, I have put in a request to my IT people to get Power Query (my workplace is using 2010, so I have to download it as an add-in). Once they approve it, I will see if I can understand how you arrived at your solution. I do see a data page output, however - did you also, as Noboffinme, manually copy each order per number of sites fulfilling? Making sure I'm seeing things correctly.

    Thanks to you both!

  5. #5
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    953

    Re: Pivot Table Site/Sales Data is in Multiple Columns

    Hi eNinjaInTraining,

    Yes I did reformat in order to create the pivot.

    My solution involves getting the source data in a more useable format as it is currently inefficient (format wise).

    If the person who supplies this data can rewrite your query, you could get it in this format.

    If not, a macro could change the data for you but this may be making the task too big for what it solves.

    Up to you.

    Cheers

+ 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 Question - Similar Data over Multiple Columns
    By thenotoriousjeff in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-14-2016, 03:40 PM
  2. Replies: 2
    Last Post: 07-30-2014, 05:50 AM
  3. Replies: 4
    Last Post: 03-27-2013, 05:56 PM
  4. Pivot table from multiple columns of data vs date
    By Dfarland in forum Excel General
    Replies: 2
    Last Post: 07-05-2011, 06:22 PM
  5. Pivot Table - Count data From Multiple Columns
    By Ashraf1 in forum Excel General
    Replies: 21
    Last Post: 02-23-2011, 08:13 AM
  6. [SOLVED] How to have multiple columns in pivot table data area?
    By mitch1962 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-24-2005, 06:07 AM
  7. [SOLVED] How to have multiple columns in pivot table data area?
    By mitch1962 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-23-2005, 10: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