+ Reply to Thread
Results 1 to 45 of 45

Learning Pivot Tables - how to display ALL transactions for customers who bought "ItemA"?

  1. #1
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Question Learning Pivot Tables - how to display ALL transactions for customers who bought "ItemA"?

    I'm new to pivot tables - I've just been watching some tutorials on youtube - but there's something I want to do, but haven't figured out how to do it yet.

    Let's say I have data like this:

    TransactionDate, CustomerName, PartNo, PartSubTotal
    01/01/16, CompanyA, Part1, $50
    05/02/16, CompanyB, Part2, $20
    15/03/16, CompanyC, Part1, $5
    20/03/16, CompanyA, Part3, $10
    22/03/16, CompanyA, Part2, $20
    25/03/16, CompanyB, Part1, $5
    29/03/16, CompanyA, Part3, $10

    Now I can get a pivot table that shows the sales per customer separated by indivual parts (CustomerName and PartNo in rows, PartSubTotal in values)...

    ...but how do I get it to display a table that shows the same thing, but ONLY for customers that purchased a "Part2"? (and I mean it still displays ALL the sales for companies that have purchased a part2, not just the part2 sales)


    (basically I'm running a promotion, and I need to identify which customers have purchased a specific product at some point in the past - only those companies will be eligible for the promotion. BUT, I want to view the sales records of those (and only those) eligible companies to see what else they're buying, as if they're buying certain other products I may not want to send them the promotion)

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    1. It would help to see the workbook.

    You get better help on your question if you add a small excel file, without confidential information.

    Please also add manualy the expected result in your file.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.


    Show manualy the result in your file, and i will take a look at it.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Please find example file attached.

    Also, this is the current pivot table showing all products sold per company:

    example2.png

    And here is the photoshopped version showing what I want to acheive:

    example3.png

    ie. The same results but only including companies that have purchased an M2500.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    You get better help on your question if you add a small excel file, without confidential information.

  5. #5
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Is the csv file no good?

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    in the csv file all data are in column A.

    That is also in you real data?

    If you want me to show the result in your file, it would be nice if you add the excel file.

  7. #7
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    ok, please find attached.
    Attached Files Attached Files

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

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    my suggestion in the appendix
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    you don't show manualy the expected result, so I just made a guess.
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Not exactly sure what you mean by:-
    "but ONLY for customers that purchased a "Part2"
    and
    "but only including companies that have purchased an M2500"
    However, is the attached what you want?

    peterrc
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Oops! attached wrong file.

    peterrc

  12. #12
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    I'll try again.

    peterrc
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Quote Originally Posted by stasinek View Post
    my suggestion in the appendix
    Quote Originally Posted by peterrc View Post
    However, is the attached what you want?
    Thanks, but these just show a summary - I want to see full details as per the photoshopped example in post #3

    Quote Originally Posted by oeldere View Post
    you don't show manualy the expected result, so I just made a guess.
    I have absolutely no idea what that is even trying to tell me, but it's certainly nothing like what I described. (the manually expected result shown in post #3)

    I was hoping there would be some way to filter it, but I guess it's just not possible then.

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Please show the results in an excel file.

    I can't open the .PNG files.

    (P.s. we are on an excelforum, so please add excel files)

  15. #15
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Please find attached...
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    See the atached file with the solution.

    Please reply.

  17. #17
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    ok, that looks good. so what option(s) do I need to choose to get that result?

  18. #18
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    in the Pivot Table filter out the empty company.

    the rest of the option you will see, if you click (just one click) in the pivot table.

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

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    see if you ok?
    Attached Files Attached Files

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

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    See attachment
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    ok, but I can't click on the pivot table in protected mode and, no offence to any of you, but I'm not disabling proteced mode on an XLS file that I just downloaded off the internet...

  22. #22
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    @jxm

    please reply to whom you are replying since you get answers of several members.

    What is the use of adding an excel-file if you are not willing to open it!

  23. #23
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Quote Originally Posted by oeldere View Post
    What is the use of adding an excel-file if you are not willing to open it!
    Because I just want to know how to do it ("just click on [whatever], tick the [option]..."), not be given a working pivot table and have to reverse engineer it. You may be happy opening XLS file from random people on the internet, but I am not. If you know how to do it, why not just explain it?

  24. #24
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    @jxm

    please reply to whom you are replying since you get answers of several members.


    rows name and partnr.

    value sum item subtotal

    in the Pivot Table filter out the empty company (in the rows name).

  25. #25
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Quote Originally Posted by oeldere View Post
    [COLOR="#FF0000"]in the Pivot Table filter out the empty company (in the rows name).
    @oeldere - how exactly do I do that? Are you saying right-click on the companie names I don't want to see, select filter then 'hide'?

  26. #26
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    click in the header with the name ROWS.

    Then you can filter (the same way, you use the filter in excel).

  27. #27
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Quote Originally Posted by oeldere View Post
    click in the header with the name ROWS.

    Then you can filter (the same way, you use the filter in excel).
    I can't find any way of filtering that makes the pivot table look like your pivot table...

  28. #28
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Then open the excel file from #16 on another PC and take a look at that file.

    After that I bet you will see what I mean.

  29. #29
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    @anyone else but oeldere

    Can someone please just explain how to do it?

  30. #30
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    I have just "tweaked" the existing pivot table in your attached file from your post #15 - see attached.

    The "actual pivot table" now matches exactly with your "photoshopped" version.

    So, yes it possible in an excel pivot table (your post #13)

    peterrc

  31. #31
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    ok, so how did you do it?

  32. #32
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    1. Click into pivot table
    2. Click pivot table tools
    3. Click design
    4. Click report layout
    5. Click tabular form
    6. Click company name in pivot table
    7. De-select all except B and E
    8. Click report layout
    9. Click compact form

    peterrc

  33. #33
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    So your solution is to start with my pivot table and then basically just manually hide the ones I don't want to see, one by one....? That's a terrible solution! It doesn't scale at all - This was just a test sample, the real data set contains thousands of companies and 10,000's of transactions.

  34. #34
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Having replicated your "photoshopped" image, as per your request, and provided a step by step guide on how to do it, as per your request, your response is:-
    "That's a terrible solution!"

    You started your post with "I'm new to pivot tables" and you have received a large number of replies from people trying to help you and I have only counted one "thank-you" from you.
    I am not sure what you expected from pivot tables but apart from that I think your attitude leaves a lot to be desired - we are only trying to help someone that is "new to pivot tables".

    peterrc

  35. #35
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    @peterrc

    great explaination in #32. Nice solutions.

  36. #36
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Quote Originally Posted by peterrc View Post
    you have received a large number of replies from people trying to help you and I have only counted one "thank-you" from you.
    When someone provides as suitable answer they'll get a thank you. If someone just keeps throwing xls files at me, after I've stated I'm not happy opening XLS files in non-protected mode, with no explanation as to how to replicate it other than "just look at it", then they just get my increasing annoyance.

    Quote Originally Posted by peterrc View Post
    I am not sure what you expected from pivot tables but apart from that I think your attitude leaves a lot to be desired - we are only trying to help someone that is "new to pivot tables".
    I'm not necessarily expecting pivot tables to do what I want, which is why I asked if it could be done. Apparently it can't. I also kind of assumed that if I explained what I was trying to do (in the original post) and asked "can pivot tables do this" it would be kind of implied that the sample data was just sample data and that I actually wanted to know how to do it so I could use it on proper data, and also kind of implied that the proper data would be much larger (otherwise why bother with a pivot table - I could just do manually a lot faster) - but again, apparently not.

    As far as I can tell, without opening the xls files, all of the solutions did nothing other than take my original pivot table and manually hid the lines I didn't want to see, which seems completely pointless. I thought the whole point of pivot tables was that it could analyse lots of data and easily display it in an understandable format, not as a starting point for a lot of time consuming manual labour in displaying the desired information...

    Yes, thanks for actually explaining how you did what you did in #32, but unfortunately what you did was of no help.

    Quote Originally Posted by oeldere View Post
    great explaination in #32. Nice solutions.
    Great explanation. Bad solution.

  37. #37
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    On this forum, people are trying to help other people with excel problems.

    First of all, what you want to achieve can be done (also with pivot table).

    I showed you the file, and explained how I made it.

    If the explaination is not clear you have to ask for clearification.

    On the forum we try to help members to understand new skills in excel.

    An excel file (instead of a csv or PNG) which do not match the real data, will bring problems with implementing the formula / solution in the real data.

    That is why I asked for the answer, manualy in the file.

    You don't add the related criteria, so a member (e.g. me) just needs to guess what you want to achieve.

    So help us, to help you by providing the asked information.

    And my answer to use another computer to test the data, is a good advice if you are scared to open excel files from internet on your computer.

    (it will not make your life easier, but that is the price you have to pay for it).

    It is also not clever to exclude members for an answer.

    Maybe that member has the perfect solution, but is not allowed (by you) to post it.

    Depending on the knowledge of pivot table, the solution of Peterrc is a nice one, so I don't agree with you that is a bad solution.

  38. #38
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Quote Originally Posted by oeldere View Post
    Depending on the knowledge of pivot table, the solution of Peterrc is a nice one, so I don't agree with you that is a bad solution.
    So if the pivot table was 50,000 lines long you think it would be a "nice solution" to sit there manually looking at the data, checking for the specific part, and then manually hiding each company that didn't match?

  39. #39
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    @jxm

    I solved that question (#38) in my file, although you did not ask for it.

    Basicly you ask for an combined solution, but only ask for the pivot table.

    The solution Peterrc proviced is a better solution than yours (since you have no solution).

    So I think it depends on how your looking at the solutions and your personal preference.

  40. #40
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Quote Originally Posted by oeldere View Post
    The solution Peterrc proviced is a better solution than yours (since you have no solution).
    I can knock together a perl script to do this in a couple of hours - I just though pivot tables might be an easier/faster solution. But perl script it is then.

  41. #41
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    If that is your conclusion and your question is solved, please mark the question solved.

  42. #42
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    You can't do what you want with a simple pivot table. You need a helper column in your data.

    In G1: "Eligible"
    In G2: =COUNTIFS($B$2:$B$23,$B$2:$B$23,$C$2:$C$23,"M2500")

    Then copy the formula to the bottom of the data.

    Pivot the whole lot, and put "Eligible" into the field area.

    From the "Eligible" filter, select 1
    Attached Images Attached Images
    Last edited by Kyle123; 03-16-2016 at 12:09 PM.

  43. #43
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    @ jxm

    I feel as if I am beginning to loose the plot here.
    Once you have determined the criteria for what you want to see, you can add thousands of records to your data-file.
    All you have to do is to refresh the pivot table and it will "display" everything that meets the criteria you set originally.
    You could even add some VBA that would automatically refresh the pivot table whenever the worksheet containing the pivot table(s) is activated/opened).

    peterrc

  44. #44
    Registered User
    Join Date
    03-13-2016
    Location
    Hertfordshire
    MS-Off Ver
    2013
    Posts
    18

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    Quote Originally Posted by Kyle123 View Post
    You can't do what you want with a simple pivot table. You need a helper column in your data.
    Awesome! Does exactly what I want - that's great thanks!

  45. #45
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Learning Pivot Tables - how to display ALL transactions for customers who bought "Item

    @oeldere
    Thanks for your comments, and support, very much appreciated.
    @Kyle123
    "Cracking" solution
    New formula for excel:-
    1 x Forum Expert + 1 x Forum Guru + 1 x Forum Contributor = 1 x Happy Registered User
    (Can this be shown in a Pivot Table?)
    Best Regards
    peterrc

+ 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] How to display customers that have not bought an item in a pivot report
    By justmeok in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 03-28-2015, 06:20 PM
  2. Display "TEXT" in "Values" field in PIVOT TABLE using VBA!!
    By meus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-05-2015, 09:56 AM
  3. Display "TEXT" in "Values" field in PIVOT TABLE using VBA!!
    By meus in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-04-2015, 06:14 AM
  4. Replies: 0
    Last Post: 10-21-2013, 05:06 PM
  5. Replies: 2
    Last Post: 01-26-2011, 06:45 AM
  6. Replies: 3
    Last Post: 04-24-2006, 01:35 PM
  7. [SOLVED] [SOLVED] How do I disable "Get Pivot Data" when working from pivot tables?
    By Frustrated excel 2003 user in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2005, 08:10 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