Multiple value columns in pivot table- mistake: repeating column 1 value counts for other columns

[Nina_B]

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

 

[Nina_B]

Dear all,

since I cannot figure out how to attach my workbook concerning, I will try to post the data here below!

Thank you so much for any possible help!

Best regards,
Nina


Problem and tables:

Data given:
Each row represents a household
A household has no, one or two transportation items
The codes in column B and C represent different transportation modes. 1-8
Column A differentiates the households per four different zones (this is not compulsory, but also does not change the problem)
What I want:
Count values of B and C and show the frequency count for each transportation item in two columns next to each other asthe format of table 3.
This works individually, i.e. if I prepare a pivot table for either B and C, the counting is correct by using A and B respectively also for row labels.
Table 1			Table 2
Zone	(All)		Zone	(All)
Count of Transport item 1		Count of Transport item 2
Row Labels	Total		Row Labels	Total
1	43		3	1
2	84		4	1
3	7		5	1
4	1		6	6
5	11		7	1
6	12		na	151
7	2		Grand Total	161
8	1
Grand Total	161
However, if I want to present B and C at the same time (ie to prevent to prepare a table for each column) the count for column C repeats the same values as for column B.
I suppose the problem is the row label, but I cannot figure it out to have Excel count either column to all possible transportation options from 1-8 (this happens indespite that column C does not cover the entire range from 1-8, even if it would, it would not work)
Here with B as row label only to show the problem
Table 3
	Values
Row Labels	Count of Transport item 1	Count of Transport item 2
1	43	43
2	84	84
3	7	7
4	1	1
5	11	11
6	12	12
7	2	2
8	1	1
Grand Total	161	161
Or I get something like this if I enter both B and C as row labels
Table 4
	Values
Row Labels	Count of Transport item 1	Count of Transport item 2
1	43	43
5	1	1
na	42	42
2	84	84
3	1	1
4	1	1
6	4	4
na	78	78
3	7	7
na	7	7
4	1	1
6	1	1
5	11	11
na	11	11
6	12	12
7	1	1
na	11	11
7	2	2
6	1	1
na	1	1
8	1	1
na	1	1
Grand Total	161	161
How could I get the individual and correct counts to show at the same time? Here it seems I can get around this by combining the tables manually.
However my data set has far more complicated questions, where I would need to add up to ten of these individual counts or more.
Therefore I appreciate any help so much!!
Thank you!
Raw data: Zone Transport item 1 Transport item 2 2 6 na 2 2 na 2 2 na 2 2 na 2 2 na 2 1 na 1 2 na 1 2 na 2 1 na 2 2 na 2 3 na 3 3 na 3 6 na 1 2 6 4 2 na 4 1 na 4 6 na 4 1 na 4 1 na 4 5 na 4 5 na 4 2 na 4 2 na 2 3 na 4 1 na 4 5 na 4 1 na 4 5 na 4 5 na 4 2 na 4 1 na 4 1 na 4 5 na 1 1 na 3 2 na 1 2 na 1 2 na 1 2 na 1 2 3 1 1 na 1 2 6 1 2 na 1 5 na 4 1 na 4 2 na 4 4 6 2 6 na 2 2 na 2 2 na 2 1 na 2 1 na 1 1 5 2 2 na 2 2 na 1 1 na 2 2 na 2 6 na 2 2 na 2 1 na 2 2 na 2 2 na 2 2 na 2 2 na 2 1 na 2 1 na 2 2 na 1 2 na 1 2 na 2 2 na 2 1 na 1 6 na 1 1 na 1 1 na 2 2 na 2 2 na 2 1 na 2 3 na 1 6 7 1 2 na 2 1 na 2 1 na 4 8 na 4 1 na 4 6 na 4 1 na 1 1 na 1 2 na 2 2 na 2 1 na 2 2 na 2 1 na 1 2 na 1 2 na 1 2 na 1 2 na 3 2 na 3 2 na 3 3 na 3 2 na 3 2 na 3 1 na 3 2 6 4 2 na 4 5 na 1 3 na 2 5 na 3 1 na 3 6 na 4 3 na 3 2 na 3 2 na 3 2 na 3 2 6 3 1 na 3 2 na 3 2 na 3 2 na 3 2 na 1 1 na 1 2 na 1 2 na 1 2 na 1 2 na 1 2 na 2 2 na 1 2 na 3 7 na 3 2 na 3 1 na 3 2 na 3 1 na 3 2 na 3 2 na 3 2 na 3 2 na 3 2 na 3 2 na 1 2 na 1 1 na 1 2 na 1 2 na 4 6 na 4 1 na 4 5 na 4 1 na 4 6 na 3 2 na 3 1 na 3 5 na 3 2 na 3 2 4 1 2 na 1 2 na 1 2 na 2 2 na 2 1 na 2 1 na 4 7 6 4 2 na 4 1 na 4 6 na <colgroup><col><col span="2"></colgroup><tbody> </tbody>

<colgroup><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

 

[RonB1111]

On the sheet with your raw data, manually type the numbers 1 thru 8 in cells E2 thru E9 and na in E10. In F1 type in Trans 1 Counts and G1 Trans 2 Counts. Now in F2, copy this formula and copy down and across:
=COUNTIF(B:B,$E2)

 

[Nina_B]

Dear Ron,

thank you very much. This, in principle, would work if I would not want to filter then also for zones of households, communities etc. That is a first which is why a Pivot table solution would be really necessary. The example above is just a very simple illustration of the issue and I would need to look at far more combinations than only overall count of transportation modes.

Plus, it does do some mistakes in counting for some reason. If you have a tip how to post my workbook here, I can show with more detail.
(Here overall counts are wrong. It omits one count for option 6 in trans 1 and it omits a few na for trans 2)

Thanks a lot for your help!

Kind regards,
Nina

Codes	Trans counts 1	Trans counts 2
1	43	0
2	84	0
3	7	1
4	1	1
5	11	1
6	11	6
7	2	1
8	1	0
na	0	143
	160	153

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[RonB1111]

You can't attach a file to a posting here, so you'd need to upload your file to a free file sharing service and then post the link here to access it. eg: Google Drive. Make sure you that you allow anyone with the link the right to download the file without a password.