How to filter 2 Columns with the same text

[groceryguy306]

Hello

I have a spread sheet that i'm keeping track of my local sons hockey teams. How do I filter so I would be able to see the same team in both the Visitor column ( Column C in excel) and in the Home Column (Column E in excel). i've tried using a pivots table but i cant get it to function properly with text in the values field. i've tried using slicers but it only allows me to filter either Column C or E and not both at the same time. I want to be able to view the entire teams away and home games

Date	Visitor	Goals V	Home	Goals H	OT	Total	OV / UD	Goals
2023-10-10	Contacts	4	Blades	2		6	Under	4-2
2023-10-10	Blades	3	Lightning	5		8	Over	3-5
2023-10-11	Contacts	1	Knights	4		5	Under	1-4
2023-10-12	Lightning	1	Blades	3		4	Under	1-3
2023-10-14	Knights	3	Contacts	5		8	Over	3-5

 

[etaf]

is this on 2013 . as shown in profile - Filter() would work on later versions , but thats on 2021/365 versions

 

[Jeffrey Mahoney]

You can use Advanced Filter (Data Advanced). Specify the Database range you want to filter and the criteria range

The criteria range is simply a copy of the headers from your table with 2 rows below it. If you put values in 2 fields on the same row and means AND. Putting values for 2 fields on different rows means OR.

Book11
	B	C	D	E	F	G	H	I	J
1	Criteria Range
2	Date	Visitor	Goals V	Home	Goals H	OT	Total	OV / UD	Goals
3				Blades
4		Blades
5
6	Database
7	Date	Visitor	Goals V	Home	Goals H	OT	Total	OV / UD	Goals
8	10/10/2023	Contacts	4	Blades	2		6	Under	2-Apr
9	10/10/2023	Blades	3	Lightning	5		8	Over	5-Mar
11	10/12/2023	Lightning	1	Blades	3		4	Under	3-Jan
Sheet2

 

[dreid1011]

Another option is to create tables for your data and make two copies and sort each one accordingly:

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Visitor	Goals V	Home	Goals H	OT	Total	OV / UD	Goals
2	10/10/2023	Blades	3	Lightning	5		8	Over	5-Mar
7
8
9	Date	Visitor	Goals V	Home	Goals H	OT	Total	OV / UD	Goals
11	10/10/2023	Contacts	4	Blades	2		6	Under	2-Apr
14	10/12/2023	Lightning	1	Blades	3		4	Under	3-Jan
Sheet4

 

[groceryguy306]

is this on 2013 . as shown in profile - Filter() would work on later versions , but thats on 2021/365 versions

Yes this is on the 2013 version

 

[groceryguy306]

Another option is to create tables for your data and make two copies and sort each one accordingly:

Yes i thought that too, It just would be nicer to have it all in one table by date

 

[dreid1011]

Not sure why I didn't think of this sooner, but you can add a helper column to your data and use the following formula, with the team name you are wanting to filter by in row 1 of that column:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Visitor	Goals V	Home	Goals H	OT	Total	OV / UD	Goals	Blades
2	45209	Blades	3	Lightning	5		8	Over	45356	1
3	45209	Contacts	4	Blades	2		6	Under	45384	1
6	45211	Lightning	1	Blades	3		4	Under	45294	1
Sheet10

Cell Formulas
Range	Formula
J2:J3,J6	J2	=IF(OR($B2=$J$1,$D2=$J$1),1,0)

 

[groceryguy306]

You can use Advanced Filter (Data Advanced). Specify the Database range you want to filter and the criteria range

The criteria range is simply a copy of the headers from your table with 2 rows below it. If you put values in 2 fields on the same row and means AND. Putting values for 2 fields on different rows means OR.

maybe i'm not putting the correct selections in each field, i cannot seem to make that work I get an error "criteria range is not valid"

 

[groceryguy306]

Not sure why I didn't think of this sooner, but you can add a helper column to your data and use the following formula, with the team name you are wanting to filter by in row 1 of that column:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Visitor	Goals V	Home	Goals H	OT	Total	OV / UD	Goals	Blades
2	45209	Blades	3	Lightning	5		8	Over	45356	1
3	45209	Contacts	4	Blades	2		6	Under	45384	1
6	45211	Lightning	1	Blades	3		4	Under	45294	1
Sheet10

Cell Formulas
Range	Formula
J2:J3,J6	J2	=IF(OR($B2=$J$1,$D2=$J$1),1,0)

thanks
that works perfect for what i need.

 

[dreid1011]

thanks
that works perfect for what i need.

You're welcome, and happy to help.