VLOOKUP Loop

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
125
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
ABCD
1EmployeeSupervisorTypeGroup
2JoeFrankEmployeeFrank
3SallySteveContractorSteve
4MarySteveContractorSteve
5JeffFrankEmployeeFrank
6LaurenJeffContractorFrank
7KarenJeffEmployeeFrank
8MikeJoeContractorFrank
9FredFrankEmployeeFrank
10RonKarenContractorFrank
11BurtKarenContractorFrank
12RalphKyleEmployeeKyle
13FrankPresidentEmployeeFrank
14StevePresidentEmployeeSteve
15KylePresidentEmployeeKyle
Sheet1
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
The Vlookup function is based on a pre-existing table of data. Looks (to me) like
the above is NOW that table. So if later on you wanted to Vlookup your Employee
Joe to bring back the Group name assigned to Joe you would use something like:

Where your cell B4 say had the value Joe
in cell C4 you could enter:

=VLOOKUP(B4, Sheet2!A2:D15, 4, False)

Just my $0.02 on the subject.

Good Luck;
 
Upvote 0
Thanks, I know how VLOOKUP works. What I am looking for help on is a loop where VLOOKUP is called repeatedly until the supervisor is "President". Then, that direct report would be put in the "Group" column.
 
Upvote 0
Providing you don't have any 'circular' relationships (eg Frank is the Supervisor of Joe and Joe is the supervisor of Frank) then you could try this macro. Test in a copy of your workbook.

<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>    <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>    <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> 0<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 = rTemp.Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> rEmp<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Peter,

That is EXACTLY what I needed! Thanks so much for your reply. I've seen this question asked a lot on this board, but never answered.

Thanks,

Steve
 
Upvote 0
Thanks again for the code you provided this morning. It turns out, in real life, some of the employee names have extra spaces in their names. So "Jones, Joe" may be "Jones , Joe".

Is there a way to compare names without spaces in the code you gave below? I don't want to modify the names, but they have extra spaces while the supervisors do not.

Thanks! Steve
 
Upvote 0
I'm not quite sure how that fits with the existing code and data layout. Could you post some sample data with this added complication together with the expected results? A similar amount of data to last time would be fine.
 
Upvote 0
It's the same data as posted above. The problem is, because of the unexpected spacing, the Supervisor can't be found in the Employee column because of the difference in spacing.

I need a way to compare the 2 columns in your code, eliminating spaces.

So "Jones , Joe" would equal "Jones, Joe, would equal "Jones,Joe"

Thanks.
 
Upvote 0
It's the same data as posted above.
I don't think so - there are no spaces (or commas) in the data above. ;)

I just wanted to be sure I knew exactly what a representative set of data and expected results looked like before putting more of my time into the problem. I'm trying to avoid spending my time suggesting something else that turns out to not quite meet your needs.
 
Upvote 0
Here is some real sample data. Notice the spacing present int he Employee column, that is not there in the Supervisor column. Thanks so much for your assistance!

Excel Workbook
ABC
1EmployeeSupervisorType
2Hamlin , Susan LLoria, MichelleEmployee
3Jones , Lorri BLoria, MichelleContractor
4Loria , MichellePresidentContractor
5Smith , SteveJones, FrankEmployee
6Lopp , LaurenHamlin, Susan LContractor
7Jones, FrankPresidentEmployee
Sheet2
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top