Using Advanced Filter, Filter for Cells that Only include "x"

[Kurtin]

Hey all,

I'm using an advanced filter to filter through a list based off of a certain cell, but its filtering the list for things that include whatever is in that certain cell. I need it to filter for rows that ONLY include the certain cell criteria in a certain column.

this is the sheet I am filtering
and i'm filtering by "1" the column I am filtering is "Group #"

Test Doc Good.xlsm
	A	B	C	D	E	F
1	Group #	#1	#2	Pair #	Length ft	Laps
2	1	Mark	Luke	P001	1500	3
3	13	Mike	Philip	P002	2500	5
4	12	Catherine	Lacey	P003	1000	2
5	11	Bill	Louise	P004	1000	2
6	21	Mary	Kate	P005	1500	3
7	15	Lawrence	Carl	P006	1500	3
8	2	Blake	Gwen	P007	1000	2
9	3	Harold	Moe	P008	1500	3
10	4	Sam	Adam	P009	2500	5
11	5	Matt	Link	P010	2000	4
12	6	Hailey	Isabelle	P011	1500	3
13	10	Pete	Ken	P012	1000	2
14	16	Jeff	Ryan	P013	1500	3
15	17	Brent	Steve	P014	1000	2
16	18	Trevor	Shane	P015	1500	3
REF

This is how the filter is coming out, its including all of the rows that include 1, but I need it to filter for the cells that ONLY contain 1.

Test Doc Good.xlsm
	A	B	C	D	E	F
1	Group #	#1	#2	Pair #	Length ft	Laps
2	1	Mark	Luke	P001	1500	3
3	13	Mike	Philip	P002	2500	5
4	12	Catherine	Lacey	P003	1000	2
5	11	Bill	Louise	P004	1000	2
6	21	Mary	Kate	P005	1500	3
7	15	Lawrence	Carl	P006	1500	3
8	10	Pete	Ken	P012	1000	2
9	16	Jeff	Ryan	P013	1500	3
10	17	Brent	Steve	P014	1000	2
11	18	Trevor	Shane	P015	1500	3
Filter

Cell Formulas
Range	Formula
A2:F11	A2	=FILTER(REF!A2:F16,ISNUMBER(SEARCH(FIN!$C$1,REF!A2:A16)),"")
Dynamic array formulas.

I have watched some youtube videos, and I tried using the LEFT function with LEN but that was just giving me the #CALC! error.

Test Doc Good.xlsm
	A	B	C	D	E	F
1	Group #	#1	#2	Pair #	Length ft	Laps
2	#CALC!
Filter

Cell Formulas
Range	Formula
A2	A2	=FILTER(REF!A2:F16,LEFT(REF!A2:A16,LEN(FIN!C1))=(FIN!C1))

 

[Fluff]

How about

=FILTER(Ref!A2:F16,Ref!A2:A16=FIN!C1,"")

 

[Kurtin]

How about

=FILTER(Ref!A2:F16,Ref!A2:A16=FIN!C1,"")

YES it worked! Thank you so much!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Kurtin]

n

You're welcome & thanks for the feedback.

Now what I need to do is duplicate the rows onto another sheet based off of the "Laps" I had a formula for it before and it was working but now it is not, should I post a new forum for this or post it here?

 

[Fluff]

should I post a new forum for this

Yes please.

 

[Kurtin]

Yes please.

The thing is, it's working in my test document (the one I post) but its not working in the document I actually need it to work in and I'm doing the exact same thing, so it doesnt make any sense

 

[Fluff]

Are you talking about the formula I suggested?

 

[Kurtin]

Are you talking about the formula I suggested?

Your formula works perfectly in both test and actual document, I mean my duplication formula, its working in my test document with your formula but in my actual document for some reason its acting like it isnt compatible, im going to keep trying, and rewrite everything from the beginning to see if it works. Thank you again for your help, I'll come back if it still doesnt work

 

[Fluff]

If you have problems, then please start a new thread.