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

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

Resources