+ Reply to Thread
Results 1 to 12 of 12

Pivot table or filter to sort data?

  1. #1
    Registered User
    Join Date
    03-07-2013
    Location
    Ilkley, UK
    MS-Off Ver
    Windows 8
    Posts
    8

    Pivot table or filter to sort data?

    Hi, I have a spreadsheet containing sales data for the year for a number of sales reps. I'd like to filter the data by rep and quarter and show selected columns, e.g. revenue, date of sale, etc. but exclude other columns, e.g. invoice date etc. Do i do this using a pivot table or by sorting? When I try to create a pivot table it wants to perform an action on the column data (e.g. sum or average it) and I want it shown as it is in the original. Thanks for your help

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Pivot table or filter to sort data?

    You probably have a Unique Invoice # for each invoice.
    Put that in the row headers, and the columns you want in the column headers, and you should have what you want.
    The alternative is to "Format as table", filter the columns you want, and then "Hide Columns".
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    03-07-2013
    Location
    Ilkley, UK
    MS-Off Ver
    Windows 8
    Posts
    8

    Re: Pivot table or filter to sort data?

    Hi Melvin - thanks for your suggestion - the table still isnt populated with data inside it. I now have the unique job numbers as rows in the table and the data i want as column headers. I want that data in columns against the job number so, for example, I have:
    job no. 123 - location: UK - date: 4 march - etc.
    job no. 456 - location: US - date: 8 april - etc.
    The pivot table wants to add the locations together not ust report them on the same line as the job number.....
    HELP!

  4. #4
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Pivot table or filter to sort data?

    A sample workbook with some mock data would be useful.
    I feel I have a good idea what you are looking for, just hard to provide a solution with no way to test possible solutions.

  5. #5
    Registered User
    Join Date
    03-07-2013
    Location
    Ilkley, UK
    MS-Off Ver
    Windows 8
    Posts
    8

    Re: Pivot table or filter to sort data?

    Cant tell if a screen shot is working...
    Essentially here it is - We are a leadership consultancy and I have a spreadsheet with jobs we sell to clients (leadership training programs):

    Job~ Client Consultant Date Qtr Days work Revenue Location
    123 Client1 Andy Jan 12 Q1 3 $3000 London
    345 Client2 Bob Feb 15 Q1 4 $4500 Singapore
    678 Client3 Andy Mar 5 Q1 2 $2500 Sydney
    987 Client3 Andy May 10 Q2 6 $7500 Hong Kong

    I'd like to filter for Andy and Q1 and then have the spreadsheet/pivot show me the date of each program, the number of days and the location. I dont need revenue data in my new data set. When i do this it sums the jobs together and returns 2 in the column not the data i need for each job.
    Is that clear?
    Attached Images Attached Images

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

    Re: Pivot table or filter to sort data?

    Hi merryquant,

    I believe you want to use the filters and sort of a simple table instead of Pivot Tables. Pivots will sum or count the numbers of things. Because you want to keep the location (which is text) you need to not use a Pivot. Simply filter a table of data by two different columns and see if that isn't what you are after.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Registered User
    Join Date
    03-07-2013
    Location
    Ilkley, UK
    MS-Off Ver
    Windows 8
    Posts
    8

    Re: Pivot table or filter to sort data?

    Thanks - I get that now - pivots want to do stuff to the data as well as report it. I want to sort and filter my original data but I have tons of columns and only need a few for this quarterly report. I'll make a new sheet and apply filters and hide columns - final question: how do i link the two so that each time the original data is updated the new sorted/filtered table is also updated?
    Thank you so much

  8. #8
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Pivot table or filter to sort data?

    If you want this "summary table" to be a seperate table from the original, then that changes everything. Especially if you want it to automatically update.
    Here is an answer for a similiar issue that should help you: http://www.excelforum.com/excel-char...23#post3124223

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

    Re: Pivot table or filter to sort data?

    If you only want to show information from a few columns and filter then look at "Advanced Filters"
    http://www.contextures.com/xladvfilter01.html

  10. #10
    Registered User
    Join Date
    03-07-2013
    Location
    Ilkley, UK
    MS-Off Ver
    Windows 8
    Posts
    8

    Re: Pivot table or filter to sort data?

    Melvin - you are amazing. This is exactly what i needed and I have created an amazing report for my boss. Thank you so much for taking all the time to understand and to explain with examples. Have a great day - Juliet

  11. #11
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Pivot table or filter to sort data?

    Thanks for the feedback. Glad it worked.
    For future posts I suggest creating a sample workbook showing what you have, and what you would like to accomplish. It is the easiest way for someone to get a clear picture of your scenario, and provide a solution for exactly what you need.

  12. #12
    Registered User
    Join Date
    03-07-2013
    Location
    Ilkley, UK
    MS-Off Ver
    Windows 8
    Posts
    8

    Re: Pivot table or filter to sort data?

    will do - thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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