Table database - Can I filter data in 2 columns at once?

Hi Everyone

I am creating an excel database and need help with some filtering help.

It is a sports score database that has home and away teams in separate columns, and I would like to be able to search for a team and have them appear in both the home and away columns of the table.

Currently I would have to choose one or the other as I'm using the basic table filter.

I have attached a sample of the database sheet, the columns in question are in orange (T1 & T2).

As an example I'd like to be able to see all references to ROK at the same time so I can see all there home and away games at the same time.

Any help is greatly appreciated.


Screenshot example.png

A couple of ways:
1) use a helper column with this formula and then filter on that

Formula:

Please Login or Register  to view this content.

2)use the advanced filter.
Select the data, then choose Advanced from the Sort & Filter group on the Data tab.
List Range is the data
Criteria Range is A1:B3

In both sheets enter the criteria in E1

Hi ByteMarks

Thanks for the reply, I'll give this a go as soon as I get home.

Thanks in advance, much appreciated.

Hi Popipipo


These sheets are fantastic, thank you so much.

Sheet 4 is what I am after.

I was wondering if you could help me me with something else on the sheet?


In the same way I sort the teams and league, can you help me so I can sort highest and lowest Goals (G1 & G2), behinds (B1 & B2), points (P1 and P2) and Margin?

This would give my database an entire data analysis side that I was hoping to do as well.


Thank you so much again, for everything to date.

Have a great day.

Hi JeteMc


Thank you for you reply and effort with my database build.

I think I like the popipipo version as I can just choose a side or league from a drop down and everything sorts without me having to use the FALSE / TRUE filter.

For me it seems just easier and quicker to use

I'll be looking to get the scores etc added on the teams and league sheet like the other team and league drop downs on that sheet, and it will give me the database I'm really looking for.

I also like how the data is separate on it's own sheet.

I really appreciate your help though, it is an eye opener for me to see what can be done, and how.

Hi Popipipo

You're right, I wasn't allowing for the double entry of data (1 game, 2 lines of results).

Thank you.

Hi all


I still need a sorting requirement the same as the teams on the teams and league tab, for the goal behinds and points.

I want to be able to sort home and away scores at the same time (to view highest points ever for example).

Currently I would have to sort twice on the sort score tab.

Can a search function the same as the team and league be added to the sort score tab?


This will allow me to sort all scoring data with one feature and eliminate the need to enter records twice more importantly.

Almost


I want to be able to choose "points" and have the points from both home and away displayed in the same way the "team" (A2) does in tab teams and league.

I want to be able to choose in the same tab sheet "teams and league" the following from a drop down list.

Goals (a combination of G3 & L3)
Behinds (a combination of I3 & M3)
Points (a combination of J3 & N3)

In E2 I have marked in yellow a new sort field where you would select Goals, Behinds & Points.

If A2 and C2 were empty and "Goals" was selected in E2, F2 sort by ascending then every entry would display both home and away with the highest ascending result at the top working down.

The idea is to be able to see every result and then be able to see the highest score either home and away depending on the combined sort option in E2.

Combined with the team and league filter in A2 & C2 a more refined result per team or league can be added.