+ Reply to Thread
Results 1 to 6 of 6

How to add columns to a Pivot table and split out values based on a column

  1. #1
    Registered User
    Join Date
    09-09-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2019
    Posts
    52

    How to add columns to a Pivot table and split out values based on a column

    Hi everyone,

    I am preparing estimates for a building project. It is useful to see estimates in the format of Description, Quantity, Unit, Rate and Total.

    Issue One

    I would like to be able to show the unit of measurement and rate in the Pivot. The unit column is a text column and the rate column isn't something that I need to add up.

    Issue Two

    I assume that achieving a solution to issue one will mean that any differences between rates and units will flag up. I.e. there are four instances of "Paint walls" in the table, but three instances in the desired Pivot.

    If anyone has a solution for this I'd be grateful.

    Thank you

    Tom
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to add columns to a Pivot table and split out values based on a column

    I can get pretty close to the desired result by:
    1. Putting the Unit and Rate fields in the Rows area
    2. Changing the Layout & Print options for both the Description and Unit fields so that they "Repeat item labels"
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    09-09-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2019
    Posts
    52

    Re: How to add columns to a Pivot table and split out values based on a column

    Hi JeteMc, thank you for this. This seems to be a very elegant solution - I was expecting a solution involving DAX.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to add columns to a Pivot table and split out values based on a column

    You're Welcome and thank you for the feedback. If the solution is acceptable, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  5. #5
    Registered User
    Join Date
    09-09-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2019
    Posts
    52

    Re: How to add columns to a Pivot table and split out values based on a column

    Hi JeteMc, FYI I tried adding solved to the the thread status but couldn't see it in the thread tools menu about my first post.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: How to add columns to a Pivot table and split out values based on a column

    That's strange, however Thank You for trying.

+ 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] Pivot Table Measure based on Column Values
    By MLAN_75 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-26-2021, 05:04 PM
  2. [SOLVED] Want to split a table based on values in a column
    By Dominic-2019 in forum Excel General
    Replies: 4
    Last Post: 04-23-2020, 05:18 AM
  3. [SOLVED] Adding a column to a pivot table that deducts one columns values from another
    By Iain Garey in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-10-2018, 10:42 AM
  4. Format a pivot table based on values of a pivot column
    By chytechplus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-25-2016, 06:32 AM
  5. Values from different columns lined up in one column, in pivot table?
    By dromedar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2013, 03:00 PM
  6. Pivot Table based on specific values from multiple columns
    By Jaypoc in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-22-2013, 12:33 PM
  7. Function to split up a table based upon the values in a reference column
    By alexdragne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2012, 02:45 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