Forum Discussion

RohitChauhan's avatar
RohitChauhan
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's avatar
    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's avatar
    RohitChauhan
    Copper Contributor
    Apr 06, 2022

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

    • HansVogelaar's avatar
      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's avatar
        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

Resources