I have a spreadsheet that looks like this (call it sheet A for now):
Then I have a sheet B that looks like this:
[Extremely large HTML example removed by admin. ~The HTML Maker add-in is for SMALL samples of your worksheet.]
What I'm trying to do is, the values in Sheet B under the Columns after "Area" will have the date of when the people's certification for the corresponding class is expired. That information comes from sheet A. I'm trying to figure out a formula or a macro where, in each of the cells of sheet B under the classes, the cell will find (in sheet A) the person's employee ID first (col A in sheet B) then see if that person has a matching entry of the class ID. If there is a match, then place the expiration date in that cell, if not, then place an "x". For example, on sheet B, cell G3 would have some type of formula (or maybe a macro can do this) where cell G3 would search sheet A for the employee ID number (in this case it's A3 which is 1036454) then see if it has a matching class ID to G3's class which is CLNRM01, on sheet A it would look under col F for that match, if it finds both the employee ID and the class ID, it will take the value from col H of the same row (the expiration date) and place that date in G3 on sheet B. The thing is, there will be many entries of the same employee ID with different class ID's, like employee 10 can have clnrm01 and shec&h while employee 12 will only have clnrom01. If I can't find a matching class ID for that cell, then i simply put an "x" there. So anyone have an idea how I can do this? I was looking at Vlookup but I don't think that will work because I have to match 2 things at the same time and there will be more than 1 entry for both values... sheet A is going to have the same employee ID for all his 5 classes, so 5 entries of the same employee ID but different class ID's for each, so i didn't think vlookup would check them all, only one... I'm not sure tho... Any help? thank you in advance (hopefully my explanation didn't just confuse more... lol)
| SSO CTS Cert Plan vs Actuals_03_16_06_MANDATED CERTS_REVA.xls | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Status | Source Code | L Name | Employee ID | F Name | Course Code | Class Name | Exp Date | Comp Date | ||
| 2 | LBLUE | 40579 | Smith | HAC53172 | Rodney | SHEC&H | CRANE & HOIST OPERATIONS | 9/14/2002 | 9/14/2000 | ||
| 3 | LBLUE | 40565 | Schiefen | HACN2546 | Charles | ESCA000858 | Crane and Hoist Operations (Goleta Only) | 2/8/2004 | 2/8/2002 | ||
| 4 | DORANGE | 40568 | Delapaz | HACE3512 | Gale | ESCA000307 | Non-Conforming Material (MRB) Indoctrination | 2/12/2004 | 2/12/2001 | ||
| 5 | LBLUE | 40579 | Hesse | HAC29360 | Paul | SHELAS-I | LASER SAFETY - INITIAL | 2/13/2004 | 2/13/2002 | ||
| 6 | LBLUE | 40579 | Grace | 1027599 | James | SHEC&H | CRANE & HOIST OPERATIONS | 2/17/2005 | 2/18/2003 | ||
| 7 | ARED | 40579 | Alleruzzo | 1035494 | Regina | SHEC&H | CRANE & HOIST OPERATIONS | 8/19/2005 | 8/20/2003 | ||
SSO Certs DATA | |||||||||||
Then I have a sheet B that looks like this:
[Extremely large HTML example removed by admin. ~The HTML Maker add-in is for SMALL samples of your worksheet.]
What I'm trying to do is, the values in Sheet B under the Columns after "Area" will have the date of when the people's certification for the corresponding class is expired. That information comes from sheet A. I'm trying to figure out a formula or a macro where, in each of the cells of sheet B under the classes, the cell will find (in sheet A) the person's employee ID first (col A in sheet B) then see if that person has a matching entry of the class ID. If there is a match, then place the expiration date in that cell, if not, then place an "x". For example, on sheet B, cell G3 would have some type of formula (or maybe a macro can do this) where cell G3 would search sheet A for the employee ID number (in this case it's A3 which is 1036454) then see if it has a matching class ID to G3's class which is CLNRM01, on sheet A it would look under col F for that match, if it finds both the employee ID and the class ID, it will take the value from col H of the same row (the expiration date) and place that date in G3 on sheet B. The thing is, there will be many entries of the same employee ID with different class ID's, like employee 10 can have clnrm01 and shec&h while employee 12 will only have clnrom01. If I can't find a matching class ID for that cell, then i simply put an "x" there. So anyone have an idea how I can do this? I was looking at Vlookup but I don't think that will work because I have to match 2 things at the same time and there will be more than 1 entry for both values... sheet A is going to have the same employee ID for all his 5 classes, so 5 entries of the same employee ID but different class ID's for each, so i didn't think vlookup would check them all, only one... I'm not sure tho... Any help? thank you in advance (hopefully my explanation didn't just confuse more... lol)
