Hi: I am somewhat new to pivot tables and have run into a problem which I think I can probably do with a pivot table but can't figure out how. Essentially I have a set of data, lets say I ask 10 people to rank their favorite four colors choosing from RED, BLUE, GREEN, and YELLOW. They are required to rank all colors as 1st through 4th. So my data block looks like:
NAME, 1ST, 2ND, 3RD, 4TH
NAME1, BLUE, GREEN, RED, YELLOW
NAME2, BLUE, RED, GREEN, YELLOW
NAME3, RED, GREEN, YELLOW, BLUE
and so on....
What I want is to know is to choose a color for the first rank and use it as a filter and be able to count the second rank colors. So I have a pivot table that uses a data filter for the 1ST RANK, then I have the second rank assigned to the "ROWS" and the "VALUES" categories and have it set up to COUNT the values. The table looks like this:
FILTER = BLUE
Row Labels SECOND RANK
BLUE 0
RED 1
GREEN 1
YELLOW 0
TOTAL 2
This is great, but I also would like to know what the counts are for the 3RD and 4TH ranks. I know that I can make a separate set of pivot tables and replace the "2ND RANK" rows and values with the 3rd and 4th rank, but is there a way to do all this in one table, something like this:
FILTER = BLUE
Row Labels SECOND RANK THIRD RANK FOURTH RANK
BLUE 0 0 0
RED 1 1 0
GREEN 1 1 0
YELLOW 0 0 2
TOTAL 2 2 2
I tried to do this by also placing the 3RD and 4TH rank columns in the VALUES box but it produces identical counts as the second rank.
NAME, 1ST, 2ND, 3RD, 4TH
NAME1, BLUE, GREEN, RED, YELLOW
NAME2, BLUE, RED, GREEN, YELLOW
NAME3, RED, GREEN, YELLOW, BLUE
and so on....
What I want is to know is to choose a color for the first rank and use it as a filter and be able to count the second rank colors. So I have a pivot table that uses a data filter for the 1ST RANK, then I have the second rank assigned to the "ROWS" and the "VALUES" categories and have it set up to COUNT the values. The table looks like this:
FILTER = BLUE
Row Labels SECOND RANK
BLUE 0
RED 1
GREEN 1
YELLOW 0
TOTAL 2
This is great, but I also would like to know what the counts are for the 3RD and 4TH ranks. I know that I can make a separate set of pivot tables and replace the "2ND RANK" rows and values with the 3rd and 4th rank, but is there a way to do all this in one table, something like this:
FILTER = BLUE
Row Labels SECOND RANK THIRD RANK FOURTH RANK
BLUE 0 0 0
RED 1 1 0
GREEN 1 1 0
YELLOW 0 0 2
TOTAL 2 2 2
I tried to do this by also placing the 3RD and 4TH rank columns in the VALUES box but it produces identical counts as the second rank.
Last edited: