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.

Student ID	Grade
111111	7
333333	5
444444	0
222222	3
555555	7
666666	6

<tbody>
</tbody>

Here is workbook 2: All student ID# in Mr.John’s class

Student ID

111111

444444

666666

<tbody>
</tbody>

Here is workbook 3: All student ID# in Ms.Sue’s class

Student ID

333333

222222

555555

<tbody>
</tbody>

And here is the expected results on new workbook 4:
(Sheet 1 Mr.John’s students with full marks of grade = 7 only)

Student ID

111111

<tbody>
</tbody>

(Sheet 2 Ms.Sue’s students with full marks of grade = 7 only)

Student ID

555555

<tbody>
</tbody>

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!