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
mikes6058Asked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
OK, come up with a way round; see attached.

On sheet called Teams I have created a list of all 20 teams. This then feeds the drop down selection in A2 of Sheet1.

Choose the required team and then refresh the Pivot; it will then list all teams that are still to play the chosen team.

This is based on an extra column added to Sheet2 extracting the relevant rows where selected team is either at Home or Away fixture.

Thanks
Rob H

PS I amended the team names in the two lists so they were consistent. The Home names had spaces after and the Away teams had spaces before. Probably result of copying from website maybe.
remaining-fixture-12042015-1.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
I fail to see how the attached file is connected with the question
0
 
mikes6058Author Commented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rob HensonFinance AnalystCommented:
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
0
 
Rob HensonFinance AnalystCommented:
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.
0
 
mikes6058Author Commented:
Hi Rob,

You are correct. I am looking to achieve exactly what you have explained in your second comment.

Rob
0
 
mikes6058Author Commented:
Brilliant solution, exactly what I needed!

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.