VLOOKUP Loop

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
117
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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;
 

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
117
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,232
Office Version
  1. 365
Platform
  1. Windows
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>
 

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
117

ADVERTISEMENT

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
 

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
117
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,232
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
117
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,232
Office Version
  1. 365
Platform
  1. Windows
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.
 

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
117
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,720
Messages
5,660,515
Members
418,581
Latest member
gtarr

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
Top