Forum Discussion
Get unique employee from 2 columns in 2 different worksheets
Hi Experts,
I have 2 worksheets "Employee- For Employee records" and "Task- For task and ownership record".
I have applied a conditional formatting in "Resources" column of "Tasks" sheet to highlight the value not available in "Employee Name" column of Employee sheet.
It is working fine for single employee names in G column, but when I give multiple employees names separated by comma, it highlights that cell also, as conditional formatting looks for exact match.
Can I use any other workaround to have multiple resources in G column and still able to compare it in A column of Employee sheet?
Please help!
Regards
Rohit
Try the following formula:
=SUM(--ISNUMBER(FIND(Employee!$A$2:$A$7,$G2)))-(LEN($G2)-LEN(SUBSTITUTE($G2,",","")))-1
8 Replies
What should happen if you have Engineer2, Engineer7 in a cell?
HansVogelaar Thanks for reply, Engineer 7 is not in Employees worksheet, so the cell should be highlighted.
Try the following formula:
=SUM(--ISNUMBER(FIND(Employee!$A$2:$A$7,$G2)))-(LEN($G2)-LEN(SUBSTITUTE($G2,",","")))-1
