• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to compare data with different sheet and extract results into multiple sheets as per criteria

itsraghus

New Member
Hello Team,
I am new to excel macros. Am recently at my new job have been assigned a task. I have gone through multiple threads within this forum and online as well, but none of the content/help seems to solve my problem. Hence reaching out to this team of expert with my problem.
Request you to kindly help me with this task:

I have enclosed the sample spreadsheet.
I start with 2 sheets - "Master" and "Scan". "Master" contains the list of all employee data. "Scan" contains the information of employees who have uploaded their "CV" and "JD" into the portal.

Ask 1:
In "Master" sheet If the External Flag (Column E) = "No" and
In "Scan" sheet if the Doc Type (Column B) = "CV"
Then
Compare - "Scan" --> CWID (Column G) with "Master" --> Name (Column A).
When CWID (Column G) from "Scan" sheet is not present in Name (Column A) in "Master" sheet, then paste the results in "Missing CV". This "Missing CV" should have CWID, Full Name and Email IDs of Employees.

Ask 2:
In "Master" sheet If the External Flag (Column E) = "No" and
In "Scan" sheet if the Doc Type (Column B) = "JD"
Then
Compare - "Scan" --> CWID (Column G) with "Master" --> Name (Column A).
When CWID (Column G) from "Scan" sheet is not present in Name (Column A) in "Master" sheet, then paste the results in "Missing JD". This "Missing JD" should have CWID, Full Name and Email IDs of Employees.

Ask 3:
The email address in "Master" sheet Column C is given as firstname.lastname@gmail.com.
In "Scan" sheet -> Name (Column A). The naming convention should be -> Last Name, First Name, CWID, CV/JD,DDMMMYYYY.PDF
If the Name (Column A) in "Scan" sheet is not having the names as per the naming convention, then the sheets 'Incorrect CV naming Format' and 'Incorrect JD naming Format', should be updated accordingly.

Ask 4:
I want to send automatic emails to all the people listed in "Missing CV", "Missing JD", 'Incorrect CV naming Format' and 'Incorrect JD naming Format' sheets.

Looking forward to hear from this forum.

Thanks and regards,
Itsraghus
 

Attachments

  • CV JD Sheet (1).xlsx
    23.2 KB · Views: 2
Back
Top