Finding Partial Text and same number

Hi All,

Please see attached spreadsheet.

Ignore T113.xlsx

There are three columns. Employee Number, Course Name (replaced with a number) and Comment Column.

If a an Employee has T114 i would like to comment next to their T113 with "Ignore".

Note: I highlighted a couple situations where the "Ignore" will come up. Also, the T114 and T113 are partial text as there will be other comments inside the course name column.

Thanks for the help!

Try .... in column D

=IF(AND(SUMPRODUCT(--($B$3:$B$48=B3),--(ISNUMBER(SEARCH("(T113)",$C$3:$C$48))))<>0,SUMPRODUCT(--($B$3:$B$48=B3),--(ISNUMBER(SEARCH("(T114)",$C$3:$C$48))))<>0,ISNUMBER(SEARCH("(T113)",C3))),"IGNORE","")

and copy down

Originally Posted by JohnTopley

Try .... in column D

=IF(AND(SUMPRODUCT(--($B$3:$B$48=B3),--(ISNUMBER(SEARCH("(T113)",$C$3:$C$48))))<>0,SUMPRODUCT(--($B$3:$B$48=B3),--(ISNUMBER(SEARCH("(T114)",$C$3:$C$48))))<>0,ISNUMBER(SEARCH("(T113)",C3))),"IGNORE","")

and copy down

Works Perfectly!

Thanks!

@AltKey: I realised myself I had redundancy in my solution with the duplicate test for "(T113)". Must not work late at night!

Dear Guru's, I have made this one- thats works the same. Why not this one?? (I am new in excel)

=IF(ISNUMBER(SEARCH("(T114)",C33)),"Ignore","")