how to extract unique list of names in filtered data

Hello Excel Gurus
I'm learning excel, I'm able to make a unique list of names with FREQUENCY function but Im Not ablle make it with filtered data
As you can see from this table when i filter Yes or NOT the list of names as to be Unique list
see on the bottom the expected result
Bye and Thank you

	A	B	C
1	name	job	yes/not
2	frank	ggggg	yes
3	mike	ffffff	not
4	frank	hhhhh	yes
5	mike	jjjjj	yes
6	frank	kkkk	not
7	julie	lllll	yes
8	mike	aaaaa	not
9	frank	eeeee	yes
10	luis	ttttt	not
11
12
13
14
15
16
17	expected result	Unique list of	names filtered data
18	Result Yes	result NOT
19	Frank	Mike
20	Mike	frank
21	Julie	luis

in your first table, you have enter name in small letters. Where as table 2 only first letter capital. I am right?
If yes, then, use formula

Formula:

Please Login or Register  to view this content.

@avk - how does this solve the OP's request? The results required are at the bottom of the table. Your formula is not going to filter those names from the original list.

Try this:

Excel 2016 (Windows) 32 bit

	A	B
18	Result Yes	result NOT
19	=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($A$18:$A18, $A$2:$A$10)+IF($C$2:$C$10<>"yes", 1, 0), 0)), "")	=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($A$18:$A18, $A$2:$A$10)+IF($C$2:$C$10<>"not", 1, 0), 0)), "")

Sheet: Sheet2

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

If you want the names capitalised, tweak the formulae like this:

=IFERROR(INDEX(PROPER($A$2:$A$10), MATCH(0, COUNTIF($A$18:$A18, $A$2:$A$10)+IF($C$2:$C$10<>"yes", 1, 0), 0)), "")

Ok noted. for unique list, any co-rrelation between name & job. If yes then explain.

Originally Posted by avk

Ok noted. for unique list, any co-rrelation between name & job. If yes then explain.

Sorry, but have you actually read the opening post? It's the third column that is in play here: the OP seems to want unique list of any name in A that has a match in C. I have provided a solution for that.

Originally Posted by AliGW

Sorry, but have you actually read the opening post? It's the third column that is in play here: the OP seems to want unique list of any name in A that has a match in C. I have provided a solution for that.

Dear AliGM, Yes i noted that, but "XLalbania" mentioned first point is : "I'm learning excel" & secondly using with "FREQUENCY", hence i am not able to what result wants Mr. XLalbania. I assuming that, (as per mentioned data) he required small letter to captal first letter. OK thanks for your valuable assistance.

@avk - see cells A17 to C21 in the table in the opening post for the results that the OP has asked for.

Try this...

Data Range

	A	B	C
1	name	job	yes/not
2	frank	ggggg	yes
3	mike	ffffff	not
4	frank	hhhhh	yes
5	mike	jjjjj	yes
6	frank	kkkk	not
7	julie	lllll	yes
8	mike	aaaaa	not
9	frank	eeeee	yes
10	luis	ttttt	not
11
12
13	Uniques
14	frank
15	mike
16	julie
17	luis
18
19
20

This array formula** entered in A14:

=IFERROR(INDEX(A$2:A$10,MATCH(0,IF(SUBTOTAL(3,OFFSET(A$2,ROW(A$2:A$10)-ROW(A$2),0)),COUNTIF(A$13:A13,A$2:A$10)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

If you filter on Yes...

Data Range

	A	B	C
1	name	job	yes/not
2	frank	ggggg	yes
4	frank	hhhhh	yes
5	mike	jjjjj	yes
7	julie	lllll	yes
9	frank	eeeee	yes
11
12
13	Uniques
14	frank
15	mike
16	julie
17

If you filter on Not...

Data Range

	A	B	C
1	name	job	yes/not
3	mike	ffffff	not
6	frank	kkkk	not
8	mike	aaaaa	not
10	luis	ttttt	not
11
12
13	Uniques
14	mike
15	frank
16	luis
17

I'll get back to you tomorrow sometime.

Getting ready to watch a hockey game!

Let's go Pens!

Good deal. Thanks for the feedback!