Excel Question

midg23

Hypothetically if I have every fixture from the past 5 seasons in Premier League inserted into a winners and losers column, obviously match winners and match losers.

If I wanted to find all the times Liverpool and Arsenal played each other I'd have to select both Liverpool and Arsenal in the winners, AND Liverpool and Arsenal in the losers column as well to show up all games.

Is there a way of just having a drop down and selecting both teams regardless of wins and losses that would just show me all of the games.

I'm thinking a pivot table but I'm not 100% sure if that's correct.

Any help is appreciated.

jimmii

If they're in different columns could you not just do it with a filter?

midg23

jimmii wrote: »

If they're in different columns could you not just do it with a filter?

They are in different columns, but I'm thinking along the lines of a "select team1" and "select team 2" which would show me all the games, regardless of win, loss, or draw between 2 teams.

jimmii

midg23 wrote: »

They are in different columns, but I'm thinking along the lines of a "select team1" and "select team 2" which would show me all the games, regardless of win, loss, or draw between 2 teams.

Yeah if you just add a filter to each of the columns and then in each column select liverpool and arsenal that should give you what you're after. Nice and simple!

midg23

jimmii wrote: »

Yeah if you just add a filter to each of the columns and then in each column select liverpool and arsenal that should give you what you're after. Nice and simple!

Mate, that's literally what I said above that I could do I was wondering was there a way of just doing it by like said, a select team option. Almost like instead of filtering

jimmii

midg23 wrote: »

Mate, that's literally what I said above that I could do I was wondering was there a way of just doing it by like said, a select team option. Almost like instead of filtering

You could set up two drop down boxes with all the teams listed and then use IF so the games only display if it matches what's in the drop down cell? I'm sure theres plenty of other ways to do it but that's just first thing I thought of and easy enough to do too.

raxy

Filtering will leave every game that either Liverpool or arsenal played in.
It is possible to do it but would need extra formulas.
If you have 2 cells where you enter the teams you want to see, could be on another tab. Than beside the fixture results you'd put 3 columns of if statement formulas.
1 if winner = either team than y otherwise n.
2 if looser = either team than y otherwise n.
That will put y in any row that has both teams playing.
In a 3rd column put if 1 & 2 = y otherwise n. Copy those down for every line & filter the last column for y & it should show any game between both teams.
Hypothetically of course.

jimmii

raxy wrote: »

Filtering will leave every game that either Liverpool or arsenal played in.
It is possible to do it but would need extra formulas.
If you have 2 cells where you enter the teams you want to see, could be on another tab. Than beside the fixture results you'd put 3 columns of if statement formulas.
1 if winner = either team than y otherwise n.
2 if looser = either team than y otherwise n.
That will put y in any row that has both teams playing.
In a 3rd column put if 1 & 2 = y otherwise n. Copy those down for every line & filter the last column for y & it should show any game between both teams.
Hypothetically of course.

I was thinking you would filter team1 and team2 and then you could filter by result too so you just see draws or away wins etc.

midg23

raxy wrote: »

Filtering will leave every game that either Liverpool or arsenal played in.
It is possible to do it but would need extra formulas.
If you have 2 cells where you enter the teams you want to see, could be on another tab. Than beside the fixture results you'd put 3 columns of if statement formulas.
1 if winner = either team than y otherwise n.
2 if looser = either team than y otherwise n.
That will put y in any row that has both teams playing.
In a 3rd column put if 1 & 2 = y otherwise n. Copy those down for every line & filter the last column for y & it should show any game between both teams.
Hypothetically of course.

You've lost me

beauf

Do a pivot. You're doing this for Fantasy Football.

tedpan

raxy wrote:

Filtering will leave every game that either Liverpool or arsenal played in. It is possible to do it but would need extra formulas. If you have 2 cells where you enter the teams you want to see, could be on another tab. Than beside the fixture results you'd put 3 columns of if statement formulas. 1 if winner = either team than y otherwise n. 2 if looser = either team than y otherwise n. That will put y in any row that has both teams playing. In a 3rd column put if 1 & 2 = y otherwise n. Copy those down for every line & filter the last column for y & it should show any game between both teams. Hypothetically of course.

Looser

davetherave

You could use the advanced filter tool

I mocked up some data with the team names as the phoentic alphabet and put in a randbetween for the scores (if you are keeping track of that) to give me 500 rows of data.


https://i.imgur.com/frO7HyN.png


Using Mike and Hotel as the Teams that I want to find all occurences of, I made a wee table up in the top right

Home Away
Team1 Team2
Team2 Team1


Then go to the Data Tab, and then Advanced Filter

The List range is in my case B1:F500

The Criteria Range is what I have put in the 2x2 table off to the side, L1:J3

https://i.imgur.com/1K90NO8.png


Using the normal filtering option of selecting each team in the home and away columns I get six rows of Mike vs Hotel, or Hotel vs Mike.

Using this I get the same expected result

davetherave

To speed things up you could even record/save it as a macro, assign it keyboard shortcut, and then once you have your team1 and team2 changed in your wee table just press Ctrl+[button] to filter it.

midg23

Thank you Davetherave. I'll check all of that out in the morning!!