Macro to find if a value matches a column, then compare with another workbook

[OrangeFanatic]

Hi everyone,

Each quarter, I receive three workbooks from different departments at my school:
Workbook 1. Student ID# and total grade
Workbook 2. List of student ID# in Mr.John’s class
Workbook 3. List of student ID# in Ms.Sue’s class

I am trying to build a macro that will produce a new 4th workbook with the following results:
A. List of student ID#s in Mr. John’s class with a total grade equal to a specific value
B. List of student ID#s in Ms. Sue’s class with a total grade equal to a specific value

Since I am focusing only on students that got 100% (full marks) on their grade, the specific value is the same for Mr.John’s and Ms.Sue’s class

Here is workbook 1: All student ID# and grade. Maximum grade value of 7 equals 100%. So students with any other grade other than 7 is not important for this macro.

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Student ID[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]111111[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]333333[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]444444[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]222222[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]666666[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Here is workbook 2: All student ID# in Mr.John’s class
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]Student ID[/TD]
[/TR]
[TR]
[TD]111111[/TD]
[/TR]
[TR]
[TD]444444[/TD]
[/TR]
[TR]
[TD]666666[/TD]
[/TR]
</tbody>[/TABLE]

Here is workbook 3: All student ID# in Ms.Sue’s class
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]Student ID[/TD]
[/TR]
[TR]
[TD]333333[/TD]
[/TR]
[TR]
[TD]222222[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[/TR]
</tbody>[/TABLE]

And here is the expected results on new workbook 4:
(Sheet 1 Mr.John’s students with full marks of grade = 7 only)
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Student ID[/TD]
[/TR]
[TR]
[TD]111111[/TD]
[/TR]
</tbody>[/TABLE]

(Sheet 2 Ms.Sue’s students with full marks of grade = 7 only)
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Student ID[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[/TR]
</tbody>[/TABLE]

Reading through the other posts across this forum and others, I understand that it is often times better to present the problem and goal rather than asking how to use a specific function as there are many ways to achieve the same thing in Excel.

I did try searching in other forums about how to use specific functions but was not able to piece together a working macro.

With your help, after the strategy is defined I pledge to edit this post with the kind of relevant keywords and phrases so that other people in the future with a similar problem can benefit from your knowledge and expertise.

Thank you in advance!