Verify TXN data(Emp,startdate,Dept Name) against Dept master(Dept Name, startdate,enddate)

Hi Everyone,

I would like to validate my transaction data against my master data.

Say I have Department Master data that has
DEPT_NAME, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
DEP1,01-JAN-2010,31-DEC-2019
DEP1,01-FEB-2020, 31-DEC-2100

I have my transaction data as following
EMPLOYER_NUMBER, JOB_START_DATE, DEPT_NAME

Basically I want to verify if employees department is valid to be consumed as of JOB_START_DATE.

Say it is accepted for the below txn data
Emp1, 01-MAR-2020, DEP1

It is not accepted for the below txn data. Note the job_start_Date which is outside master data range
Emp1, 05-JAN-2020, DEPT1

how do I acheieve this using VBA code?


I have thousand of master and lakhs of txn data. I want to validate and mark whether txn data is all good.

Please advice.

Thanks
Manikandan R

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you