Filtering from multiple columns

[mjs188]

I am doing a datasheet for each NBA game in 2017. I am using the excel table function to filter through teams so that i can easily see total track record of each team.

However, i have listed each team as either the "HOME" or "AWAY" team in separate columns.

If i wanted to filter for all of CLEVELAND CAVS(CLE) games from both HOME and AWAY columns is there a way for me to use the filter command to filter for all CLE games in both columns?


Currently, i am having to filter for CLE in HOME, then refilter for CLE in AWAY.

Any advice would be greatly appreciated.
THANK YOU in advance!

table1.png

[shivya]

Hello mjs888,

you can't filter two columns at the same time.you need to do it one by one. If you want the entries of CLE (HOME and AWAY) then you need another column with formula. If you want, I can do the same for you if you attach the file.

[mjs188]

Hi Shivya,

Thank you for answering my question. Greatly appreciated!

If you could do that for me that would be fabulous!

Kind regards,
Mjs188

[shivya]

I couldn't attach excel sheet.

step 1 : create drop down of list values(games).
step 2 : In next line, place the headings DATE DAY HOME PT AWAY PT2 LINE
step3 : in drop down, you can change the values of games.
step 4 : Based on that value, below result will be auto updated automatically.
step 5 : Type this formula =IF(Table3[@HOME]=$L$4,OFFSET(Table3[[#Headers],[DATE]],MATCH($L$4,F5:F7,0),2),"") - > ctrl + shift + enter.
step6 : Drag down.

You need to change the cols manually because match function counts from 1 but offset counts from 0.

After updating my browser, I will be able to attach my excel sheet.

[popipipo]

Or take a look at this.