mikes6058
asked on
pivot table help - fixture list
Hi all,
Attached is a sheet containing all remaining fixtures (matches) for the EPL.
How can I use pivot tables to be able to view all the remaining fixtures for each individual team in the league.
So far I have only been able to view either the remaining home or away fixtures for each team but not a combination of both.
See attached,
Rob
remaining-fixture-12.04.2015.xlsx
Attached is a sheet containing all remaining fixtures (matches) for the EPL.
How can I use pivot tables to be able to view all the remaining fixtures for each individual team in the league.
So far I have only been able to view either the remaining home or away fixtures for each team but not a combination of both.
See attached,
Rob
remaining-fixture-12.04.2015.xlsx
I fail to see how the attached file is connected with the question
ASKER
For example from row 2 QPR Chelsea would represent a fixture. I want to produce a pivot table to display all of the fixtures for each team (20 teams)
For Chelsea the fixtures would be as below. I want a pivot that will provide this kind of breakdown for each of the 20 teams
QPR
Man Utd
Arsenal
Leicester
Crystal Palace
Liverpool
West Brom
Sunderland
For Chelsea the fixtures would be as below. I want a pivot that will provide this kind of breakdown for each of the 20 teams
QPR
Man Utd
Arsenal
Leicester
Crystal Palace
Liverpool
West Brom
Sunderland
With only two columns of names, it is very limited as to what you can achieve.
You have two options:
- both columns as row labels, they can then be grouped such that the Home team is the header and then Away team as a node below it. With current data this option shows for the first couple of entries as:
Arsenal
Chelsea
Sunderland
Swansea
West Brom
Aston Villa
Burnley
Everton
West Ham
-Home teams as rows and Away teams as columns. This gives 20 rows and 20 columns.
Thanks
Rob H
You have two options:
- both columns as row labels, they can then be grouped such that the Home team is the header and then Away team as a node below it. With current data this option shows for the first couple of entries as:
Arsenal
Chelsea
Sunderland
Swansea
West Brom
Aston Villa
Burnley
Everton
West Ham
-Home teams as rows and Away teams as columns. This gives 20 rows and 20 columns.
Thanks
Rob H
OK, I think I might have misunderstood the requirement.
With the above, it has listed the Away teams for a particular team's Home fixtures.
I think you are saying you want all fixtures listed for a particular team. For the two examples, Arsenal also appears as an Away fixture against Hull but you want Hull listed under Arsenal as well.
If that is the case, you will get duplicate data. You will see Arsenal as the "header" team and Hull listed below. You will also then see Hull as the header team and Arsenal listed below.
With the above, it has listed the Away teams for a particular team's Home fixtures.
I think you are saying you want all fixtures listed for a particular team. For the two examples, Arsenal also appears as an Away fixture against Hull but you want Hull listed under Arsenal as well.
If that is the case, you will get duplicate data. You will see Arsenal as the "header" team and Hull listed below. You will also then see Hull as the header team and Arsenal listed below.
ASKER
Hi Rob,
You are correct. I am looking to achieve exactly what you have explained in your second comment.
Rob
You are correct. I am looking to achieve exactly what you have explained in your second comment.
Rob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant solution, exactly what I needed!
Thanks
Thanks