+ Reply to Thread
Results 1 to 4 of 4

Multiple value columns in Pivot table- mistake: repeating column1 value counts in columnn2

  1. #1
    Registered User
    Join Date
    07-27-2014
    Location
    germany
    MS-Off Ver
    Mac 2011
    Posts
    2

    Multiple value columns in Pivot table- mistake: repeating column1 value counts in columnn2

    Dear all,

    I have tried now for several days to solve this problem and would be so thankful for any help.

    I use Excel Mac 2011 and have the problem that if I want to display the count for values in multiple columns side by side, the count for column 2 repeats the same numbers as for count in columns 1.

    An example I have: in the raw data, there are households who can hold up to 2 types of transportation items. There are 2 columns for each indicating type of transport households have. Q1_1 and Q1_2.
    Each of these options are coded i.e. bike, car, bus are numbers from 1-8 as there are 8 options.

    What I want: I want to create a pivot table that shows the counts for all households for Q1_1 and Q1_2 i.e. the two types of transport they may possess out of 8 different options.

    What I did: There is no problem in creating the counts for either Q1_1 or Q1_2. Row label then is either of the one and the matching count of in the value field. It shows me that 5 households have a bike, 18 a car etc.
    But if I want to display both counts next to each other, it presents the same counts for Q1_2 as for Q1_1, i.e. they show the 5 and 18 etc again. It seems the problem is the label row. I tried all solutions indicated on the web, fora etc e.g. to move both Q1_1 and Q1_2 into row label, then move Q1_2 into columns labels. That however just sorted the transportation mode from Q1_2 underneath Q_1 which is not what I want. I also tried to use 'sum of' 'count numbers' to see if it changes the way it counts the second transportation item. No success.

    A note: the range from the transportation items 1-8 is not covered by the second item. However, in other examples i.e. yes, no questions I have the same issue even though answer options are completely congruent.

    Is there anyone please who can help me? I have tried all I can and it seems I need to give up.

    Thank you!!

    Best, Nina

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Multiple value columns in Pivot table- mistake: repeating column1 value counts in colu

    You really need to post an Excel sheet to get some help!!
    Glenn



  3. #3
    Registered User
    Join Date
    07-27-2014
    Location
    germany
    MS-Off Ver
    Mac 2011
    Posts
    2

    Re: Multiple value columns in Pivot table- mistake: repeating column1 value counts in colu

    Dear all,

    I hope this helps to illustrate the problem.

    Thanks so much for any help!

    Best regards,
    Nina

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,888

    Re: Multiple value columns in Pivot table- mistake: repeating column1 value counts in colu

    You would need to restructure your data to do that.

    New data on data sheet and pivot table on sheet1
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

+ 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. Replies: 2
    Last Post: 05-08-2013, 04:56 PM
  2. Table/function that counts number of occurrences within multiple columns
    By Superion in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2013, 06:38 PM
  3. Pivot table with columns listing counts
    By theThirdMan in forum Excel General
    Replies: 2
    Last Post: 12-10-2012, 06:20 PM
  4. Pivot table - SUM of COUNTS?
    By nick_danger in forum Excel General
    Replies: 1
    Last Post: 03-10-2008, 11:43 PM
  5. Replies: 1
    Last Post: 02-10-2006, 06:15 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