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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikes6058Author Commented:
Brilliant solution, exactly what I needed!

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.