Hi everyone. I have found myself in a spiral of inadequate formulas. I do hope someone can help me out. So this is what we have.
ID numbers in file are reconciled with a download of ID numbers from the Regulator website. I want to have an entry populated in the 'Validity' column after matching the ID numbers and then confirming the status as OK (if GENR) and INVALID (if 'Not valid).
If an ID number listed (on file) is not in the ID number download column from the Regulator, the ID number in the 'on file' column is highlighted.
There will be multiple copies of ID numbers listed against jobs in the file column, and only a single ID number in the download column.
Thanks to everyone who reads and provides comments/feedback.
Thank you for the quick assistance. I didn't explain myself very well, so I thought I'd provide a little more info on what I need and an extract of the file
Background
I am required to check that each of the employees have a valid ID before performing the assigned job. (note that an employee may have more than one job for the day - column A has 200 lines).
I upload a csv of employee ID numbers (column D 162 lines) to the regulators website.
The Regulator then provides a file containing columns D and E with E being the status.
Depending on the status in column E, a response is generated in column C (Approved) of "OK" or "INVALID"
My formula doesn't account for the employee ID existing but being 'not current' and therefore employee cannot perform work that day.
So Column C = the result of answering 2 questions.
is the employee ID in column A also in column D;
is the status of the employee ID in column A 'not current'
Answers:
Query 1 above is true(then lookup status),
status is 'not current' (then return "INVALID in column C - next to job No.), OR
status is any value other than 'not current' (then return "OK" in column C - next to job No.)
Query 1 above is false (then return "INVALID" in column C - next to job No.)
i.e. in the example below C15 should = INVALID (and against all other jobs for that employee ID value in column A)
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.