VLOOKUP Loop

sscornav

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

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
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
113
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
45,918
Office Version
365
Platform
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
113

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
113
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
45,918
Office Version
365
Platform
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
113
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
45,918
Office Version
365
Platform
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
113
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,199
Messages
5,509,795
Members
408,755
Latest member
bobtaske

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top