VLOOKUP Loop

[sscornav]

I have an employee roster and need to fill in a value in column D of the person's ultimate boss that reports to the President.

To do this I think I would have to do a VLOOKUP, check to see if that person reported to the president, and if not do another VLOOKUP until I met the condition.

I need some help with the macro and loop. Please!!

Here is what the sheet looks like, I filled in the answers by manually calculating them in YELLOW, but I need to automate this.

Thanks, Steve

Excel Workbook
	A	B	C	D
1	Employee	Supervisor	Type	Group
2	Joe	Frank	Employee	Frank
3	Sally	Steve	Contractor	Steve
4	Mary	Steve	Contractor	Steve
5	Jeff	Frank	Employee	Frank
6	Lauren	Jeff	Contractor	Frank
7	Karen	Jeff	Employee	Frank
8	Mike	Joe	Contractor	Frank
9	Fred	Frank	Employee	Frank
10	Ron	Karen	Contractor	Frank
11	Burt	Karen	Contractor	Frank
12	Ralph	Kyle	Employee	Kyle
13	Frank	President	Employee	Frank
14	Steve	President	Employee	Steve
15	Kyle	President	Employee	Kyle
Sheet1

 

[Peter_SSs]

OK, thanks. A couple of further questions.

1. Is the extra space only ever 1 space and immediately before the comma? Can it be more than one space? Can the extra space(s) turn upelsewhere? For example,
Hamlin*,**Susan***L where I have used * to represent a space.

2. You indicated previously you didn't want to change the name to eliminate the extra space(s). I'm wondering why that is? It would be simple to do a Find/Replace (either manually or as part of the code) in that first column to Find " ," and Replace with ","

 

[sscornav]

I have found one or several spaces. The list is about 3500 people long. It seemed safest to compare the list without modifying them.

 

[Peter_SSs]

I'm not sure about not modifying the data to tidy up the spaces. I think I would be trying to standardise it. However, if that is representative data and leaving column A as is, try this:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Ultimate_Boss()<br>    <SPAN style="color:#00007F">Dim</SPAN> rEmployees <SPAN style="color:#00007F">As</SPAN> Range, rEmp <SPAN style="color:#00007F">As</SPAN> Range, rSup <SPAN style="color:#00007F">As</SPAN> Range, rTemp <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> aOrigData<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rEmployees = Range("A2", Range("A" & Rows.Count).End(xlUp)) <SPAN style="color:#007F00">'<-- Change column if needed</SPAN><br>    aOrigData = rEmployees.Resize(, 2).Value<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Replace_Data<br>    rEmployees.Resize(, 2).Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchFormat:=<SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rEmp <SPAN style="color:#00007F">In</SPAN> rEmployees<br>        <SPAN style="color:#00007F">Set</SPAN> rSup = rEmp.Offset(, 1)<br>        <SPAN style="color:#00007F">Set</SPAN> rTemp = rEmp<br>        <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> rSup.Value = "President" And <SPAN style="color:#00007F">Not</SPAN> rSup = rTemp<br>            <SPAN style="color:#00007F">Set</SPAN> rTemp = rSup<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rSup = rEmployees.Find(What:=rTemp.Value, LookIn:=xlValues, LookAt:=xlWhole, _<br>                    MatchCase:=False, SearchFormat:=False).Offset(, 1)<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Replace_Data<br>        <SPAN style="color:#00007F">Loop</SPAN><br><br>        <SPAN style="color:#00007F">If</SPAN> rSup = rTemp <SPAN style="color:#00007F">Then</SPAN><br>            rEmp.Offset(, 3).Value = "Not Found"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            rEmp.Offset(, 3).Value = Replace(rTemp.Value, ",", ", ")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> rEmp<br>    <br>Replace_Data:<br>    rEmployees.Resize(, 2).Value = aOrigData<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[sheetspread]

Nice code, it taught me something also!