+ Reply to Thread
Results 1 to 4 of 4

Unable to analyse table with binary and multiple choice data with a pivot table

  1. #1
    Registered User
    Join Date
    11-06-2020
    Location
    Buenos Aires, Argentina
    MS-Off Ver
    2019
    Posts
    2

    Unable to analyse table with binary and multiple choice data with a pivot table

    Hi all!

    I am having troubles converting some data stored in an Excel table into a meaningful pivot table.

    The table contains data from post-sales surveys with clients + other internal and external data.

    I have built the table ('original table' tab in the attached Excel) so that every row represents a sales opportunity. There are columns for the client name, the outcome (win or loss), the date, the region, the product sold, and the competitor that won or lost.

    Troubles start with columns H3 to M3. Each client can select up to 3 reasons for which they chose our product or, alternatively, a competitor's product. I have created a column for each possible reason. While this makes it easy to filter the data in the table; I am unable to aggregate this data with a pivot table (e.g. I cannot aggregate all win reasons by region or client into a single pivot table column) and I am forced to do manual calculations every time I need to do some analysis.

    So I thought I was maybe using the wrong table and I have created a new one that has a row for each win/loss reason a client selected ('new table' tab in the Excel).
    While I can finally summaries win/loss reasons through a pivot table now; I have lost the ability to count unique opportunities (e.g I cannot say how many times a specific client hasn't chosen us, as client names are repeated several times for the same opportunity).

    Is anyone able to suggest a way to overcome this?
    Thanks a lot!
    Attached Files Attached Files
    Last edited by mark1987__; 11-06-2020 at 03:15 PM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,282

    Re: Unable to analyse table with binary and multiple choice data with a pivot table

    Hello mark1987 and Welcome to Excel Forum.
    This proposal adds a column to the "new table".
    The added column ("Count") is populated using: =COUNTIFS(A$4:A4,A4)
    The added column is then used as the filter for the pivot table and only 1's are selected.
    Client Name is placed in the Rows area and Outcome is placed in both the Columns and Values area.
    If this isn't what you are looking for in finding the number of times a specific client has or has not chosen your company then please manually show us what you would like the result to display so that we may attempt to replicate in pivot table.
    Please update your profile to include the version of Excel that you are using (i.e. 2010, 2016, 365 etc.) as xp is not a version of Excel and knowing which version you are using may make it easier to help.
    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
    11-06-2020
    Location
    Buenos Aires, Argentina
    MS-Off Ver
    2019
    Posts
    2

    Re: Unable to analyse table with binary and multiple choice data with a pivot table

    Thanks so much, JeteMc! It worked perfectly!


    Thanks for the heads-up as well; I have edited my profile now.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,282

    Re: Unable to analyse table with binary and multiple choice data with a pivot table

    You're Welcome and thank you for the feedback. 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.

+ 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] Unable to get data from Pivot Table via GETPIVOTDATA
    By PB4InfoCentral in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-14-2020, 12:16 PM
  2. [SOLVED] Pivot table - Unable to navigate to source data from pivot table
    By Richavlaues in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2016, 10:37 AM
  3. [SOLVED] Analyse each of the groups in a Data Table and send results to a Summary Table
    By PeterR in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-20-2015, 10:26 PM
  4. Pivot Table to analyse sales figures
    By mj04 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-21-2014, 09:54 PM
  5. Potential ways to format binary data with a pivot table.
    By Ali2013 in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 04-17-2013, 09:10 AM
  6. Replies: 5
    Last Post: 01-05-2011, 11:37 PM
  7. Multiple choice pivot table help.
    By amean_n in forum Excel General
    Replies: 1
    Last Post: 06-25-2010, 06:33 AM

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