Compare Master JobCodes sheet against NEW employees sheet on third sheet

Jennifer Mahoney

HI there

I'm trying to determine if a jobcode on one sheet is NOT found on the Master jobcode sheet

and populate them on a 3rd sheet

*note if a 3rd sheet is not possible, I could settle for placing the formula on the Master jobcode sheet

NEW EMPLOYEES SHEET - imported from excel into SS

MASTER JOBCODE SHEET - should contain all possible job codes

On a 3rd sheet I need to populate a list of *new codes (i.e. not found on the Master sheet) with their corresponding position title.

In the above example it would return that B1 | Dir Clinical Research row since it does NOT match against our master sheet of job codes

Need help with a cross sheet reference formula that will pull those instances.

Thank you

Jennifer

MedaUser

In order to pull that kind of unique data to another sheet, the formula needs something to reference within itself first (i.e. VLOOKUP reference or IF reference). Since you're looking for a unique value that only exists on the imported sheet, then I would suggest one of the following solutions:

1. If your company has DataMesh, then this will pull the unique values to the sheet automatically for you.
2. Without DataMesh, I think you should add a new column to the NEW EMPLOYEES SHEET (after importing) labeled Unique Position. Then, write a column formula that reads like this:

=IFERROR(IF(VLOOKUP([NEW EMPLOYEES SHEET-JOB CODE]@row, {REFERENCE MASTER JOB CODE SHEET-BOTH COLUMNS}, 1, false) = [NEW EMPLOYEES SHEET-JOB CODE]@row, "No"), "Yes")

Once you covert that formula to a column formula, you can either set conditional formatting or a filter to search for the "Yes" answers, which will provide you with your search. If you need another sheet, then I'd suggest doing this and filtering for the "Yes" answers, then copy/pasting them all to the new sheet.

MedaUser

In order to pull that kind of unique data to another sheet, the formula needs something to reference within itself first (i.e. VLOOKUP reference or IF reference). Since you're looking for a unique value that only exists on the imported sheet, then I would suggest one of the following solutions:

1. If your company has DataMesh, then this will pull the unique values to the sheet automatically for you.
2. Without DataMesh, I think you should add a new column to the NEW EMPLOYEES SHEET (after importing) labeled Unique Position. Then, write a column formula that reads like this:

=IFERROR(IF(VLOOKUP([NEW EMPLOYEES SHEET-JOB CODE]@row, {REFERENCE MASTER JOB CODE SHEET-BOTH COLUMNS}, 1, false) = [NEW EMPLOYEES SHEET-JOB CODE]@row, "No"), "Yes")

Once you covert that formula to a column formula, you can either set conditional formatting or a filter to search for the "Yes" answers, which will provide you with your search. If you need another sheet, then I'd suggest doing this and filtering for the "Yes" answers, then copy/pasting them all to the new sheet.

Jennifer Mahoney

@MedaUser - Thank you for help I used option 2 for a solution.

MedaUser

Great, @Jennifer Mahoney! Happy to help. If you wouldn't mind, could you mark my answer as accepted?