Forum Discussion

Copper Contributor

Apr 06, 2022

Solved

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...

excel

Formulas and Functions

HansVogelaar
Apr 06, 2022
RohitChauhan

Try the following formula:

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

RohitChauhan

Copper Contributor

Apr 06, 2022

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

HansVogelaar

MVP

Apr 06, 2022

RohitChauhan

Try the following formula:

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

RohitChauhan
Copper Contributor
Apr 06, 2022
Hi HansVogelaar,
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
- HansVogelaar
  MVP
  Apr 07, 2022
  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.
  - RohitChauhan
    Copper Contributor
    Apr 07, 2022
    Thanks Hans,
    I guess that's what I have to do 🙂
    
    Regards
    Rohit
RohitChauhan
Copper Contributor
Apr 06, 2022
HansVogelaar Just wow, It is working like a charm.
Thanks a ton, you have saved me I don't know how many hours.
Grateful!