Compare Master JobCodes sheet against NEW employees sheet on third sheet
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
Best Answer
-
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:
- If your company has DataMesh, then this will pull the unique values to the sheet automatically for you.
- 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.
Travis C, PMP
Smartsheet Leader with 5+ years of SS experience
Let's connect: LinkedIn - Travis C.
If my answer was sufficient, pleaseupvote and mark my response as answered.
Answers
-
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:
- If your company has DataMesh, then this will pull the unique values to the sheet automatically for you.
- 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.
Travis C, PMP
Smartsheet Leader with 5+ years of SS experience
Let's connect: LinkedIn - Travis C.
If my answer was sufficient, pleaseupvote and mark my response as answered.
-
@MedaUser - Thank you for help I used option 2 for a solution.
-
Great, @Jennifer Mahoney! Happy to help. If you wouldn't mind, could you mark my answer as accepted?
Travis C, PMP
Smartsheet Leader with 5+ years of SS experience
Let's connect: LinkedIn - Travis C.
If my answer was sufficient, pleaseupvote and mark my response as answered.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!