VBA: Vlookup and Separate

[unknownymous]

Hi Guys,

I'm trying to create a macro but having a hard time creating as I'm more on recording and there are some conditions that needs to be met.

I have an excel file with 3 tabs:

Sheet 1: Master Tab

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2&3: Source 1 & 2 (Format is the same but the data is different - see below sample)

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Score[/TD]
[TD]Note[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Jack[/TD]
[TD]88[/TD]
[TD]Student[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Sheena[/TD]
[TD]85[/TD]
[TD]Student[/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]May[/TD]
[TD]70[/TD]
[TD]Teacher[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]Student[/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]Sam[/TD]
[TD]0[/TD]
[TD]Student[/TD]
[/TR]
</tbody>[/TABLE]


Task:
1. I need to look up both Sheet 2 & 3 against Sheet 1 by using ID column as ref.
2. If the cell is not in the lookup range and the score is zero, it will be put under the "ZERO" section.
3. If the cell is not in the lookup range and there is a score, then the row will be highlighted and another sheet will be created name "Add" (should be added in the Master tab) which contains the ID, Name & Source Tab Sheet Name (see below).
4. If the Note Column is "Teacher", either there score there is a score on none, it will be put under the "EXCLUDED" section
5. See below Final Result

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Score[/TD]
[TD]Note[/TD]
[TD]LookUp Result[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Jack[/TD]
[TD]88[/TD]
[TD]Student[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]Sam[/TD]
[TD]0[/TD]
[TD]Student[/TD]
[TD]005[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Sheena[/TD]
[TD]85[/TD]
[TD]Student[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZERO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]Student[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EXCLUDE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]May[/TD]
[TD]70[/TD]
[TD]Teacher[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

"Add" Tab

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Source Tab[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Sheena[/TD]
[TD]Source 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Will appreciate any help you could give.


Thank you!