How to extract the students' names that passed the test only

[josh44]

How to extract the students' names that passed the test only

rrrr.JPG

[Pete_UK]

Put this formula in B1:

=COUNTIF(A$1:A1,"Pass*")

and copy down to the bottom of your list of results. Then you can use this formula in C1:

=IFERROR(TRIM(SUBSTITUTE(INDEX(A:A,MATCH(ROWS($1:1),B:B,0)),"Pass:","")),"")

Copy down to get your list of student names who have passed.

Hope this helps.

Pete

[josh44]

Thanks Pete, it is great and it really worked
Is there any chance that we merge the 2 formulas in one column
But again thanks Pete for your help
Josh

[FioreMatto]

Good morning
with this data source in C1 to drag down

=IFERROR(TRIM(MID(INDEX($A$1:$A$6,AGGREGATE(15,6,ROW($A$1:$A$6)/(LEFT($A$1:$A$6,FIND(":",$A$1:$A$6)-1)="Pass"),ROW($A1))),FIND(":",INDEX($A$1:$A$6,AGGREGATE(15,6,ROW($A$1:$A$6)/(LEFT($A$1:$A$6,FIND(":",$A$1:$A$6)-1)="Pass"),ROW($A1))))+1,LEN(INDEX($A$1:$A$6,AGGREGATE(15,6,ROW($A$1:$A$6)/(LEFT($A$1:$A$6,FIND(":",$A$1:$A$6)-1)="Pass"),ROW($A1)))))),"")

If instead you put the results in a column and in the other the names for example in column E the results and in column F the names in H1 to drag down

=IFERROR(INDEX($F$1:$F$6,AGGREGATE(15,6,ROW($E$1:$E$6)/($E$1:$E$6="Pass"),ROW(A1))),"")

[josh44]

Mr. Fiore
Wow Wow, Thank you so much for your help. It is more than great. Solved my problem. You have a nice and great day.

Josh

[kvsrinivasamurthy]

Try this single formula In C1 then copy down.

Please Login or Register  to view this content.

[AlKey]

Try this...
No helper columns needed; regular formula
Enter formula in C1 and copy down.

Please Login or Register  to view this content.

v	A	B	C
1	Fail: Len		Josh
2	Pass: Josh		Tom
3	Pass: Tom		John
4	Fail: Marry
5	Fail: Scott
6	Pass: John

[josh44]

Thanks a lot to Mr kvsrinivasamurthy and Mr AlKey for your perfect solutions and added Reputation for all of you. Thanks again
h