Get unique employee from 2 columns in 2 different worksheets

[RohitChauhan]

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

[#2]

@RohitChauhan 

What should happen if you have Engineer2, Engineer7 in a cell?

[#3]

@Hans Vogelaar Thanks for reply, Engineer 7 is not in Employees worksheet, so the cell should be highlighted.

[#4 · Accepted solution]

@RohitChauhan 

Try the following formula:

=SUM(--ISNUMBER(FIND(Employee!$A$2:$A$7,$G2)))-(LEN($G2)-LEN(SUBSTITUTE($G2,",","")))-1

[#5]

@Hans Vogelaar Just wow, It is working like a charm.

Thanks a ton, you have saved me I don't know how many hours.

Grateful!

[#6]

Hi @Hans Vogelaar,
Apologies for not stating the problem in dummy data correctly, when I applied the same formula in my project data, it is highlighting all the cells in G column, as A column of Employee sheet actually has duplicate employees for different dates.

I am not able to figure it out how to modify the formula to work in my condition!
Can you please help again?

Regards

Rohit

[#7]

@RohitChauhan 

You can use the UNIQUE function to create a list of unique engineer names, and use that list in the formula instead of the range in column A.

[#8]

Thanks Hans,
I guess that's what I have to do :)

Regards
Rohit

[#9]

@RohitChauhan 

Or use

=SUM(--ISNUMBER(FIND(UNIQUE(Employee!$A$2:$A$7),$G2)))-(LEN($G2)-LEN(SUBSTITUTE($G2,",","")))-1