Results 1 to 4 of 4

Thread: Multiple columns values- mistake: repeats column 1 value counts for all other columns

  1. #1

    Multiple columns values- mistake: repeats column 1 value counts for all other columns

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version

    can you post a workbook with your data, and the pivot that you have created, and just a few sample lines of what you actually want?

  3. #3
    Dear Bob and all,

    of course, please find the example worksheet showing the data, the pivot table and the problem explained with tables attached.

    Thanks again for any possible help. It seems that there must be just a very easy solution, but I simply cannot figure it out and I have spent days on this.

    Thank you and best regards,

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Excel Version
    Nina_B, please have a read of then include links here to where you have cross posted.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts