Formula/Vbasic Search

[nldstorm]

Need some help. I am working with a large flat file listing reporting relationships. What I would like to find is what I refer to below as the Final Supervisor. Each employee eventually reports up to a Final Supervisor. Do you know of a formula/ Vbasic that I can run so that for each employee I am able to list the Final Supervisor? Below is a simple extract where I manually inserted the Final Supervisor in Column F. The table below only lists one Final Supervisor however in reality I have about 15 Final Supervisors. Furthermore, the database below is sorted for visual purposes but in my main database there is no logical sort present, just a large flat file with about 20,000 employees. The Final Supervisor is not listed in the database
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Any ideas?<o></o>
<o></o>
Thanks
<TABLE style="WIDTH: 539pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=715 border=0 x:str><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 11pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=14 height=17> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=114>A</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=130>B</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=101>C</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=173>D</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=86>E</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=97>F</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: gray" width=114>Personnel number:</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: gray" width=130>Last name:</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: gray" width=101>First name:</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: gray" width=173>Supervisor:</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: gray" width=86>Spv pers num:</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: gray" width=97>Final Supervisor</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=114>10017</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=130>Clark</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=101>Leontyne</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=173>Smith, Jayson</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=86>6996</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=97>Bell, Alexander</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=114>6996</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=130>Smith</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=101>Jayson</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=173>Bush, George</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=86>62020</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=97>Bell, Alexander</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=114>62020</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=130>Bush</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=101>George</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=173>Brown, Bobby</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=86>64577</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=97>Bell, Alexander</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=114>64577</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=130>Brown</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=101>Bobby</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=173>Bell, Alexander</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=86>53924</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=97>Bell, Alexander</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=114>53924</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=130>Bell</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=101>Alexander</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=97>Bell, Alexander</TD></TR></TBODY></TABLE>

 

[Ruddles]

Ah, no, in F2 you have to enter: =IF(ISNA(VLOOKUP(E2,$A$2:$E$23,5,FALSE)),"",VLOOKUP(E2,$A$2:$E$23,5,FALSE))

You have: =IF(ISNA(VLOOKUP(E3,$A$3:$E$23,5,TRUE)),"",VLOOKUP(E3,$A$3:$E$23,5,TRUE))

I would start with FALSE as the match parameter and only change it to TRUE when you know the FALSE version works.

 

[mikerickson]

I think that one issue that all these methods have is that if Smith, John (employee No. 111111) is a supervisor in Accounting and Smith, John (employee No. 222222) is a supervisor in Production, we can't distinguish between them in the Immediate Supervior column.

Also, you say that this is a one off situation, but won't everything need to be redone when there are either new hires or promotions.
Also, why can't a UDF be used for a one off?

 

[Ruddles]

Mine uses the personnel numbers to make the connections, and only shows the names of the final supervisors.

 

[mikerickson]

The OP shows that Employee # 10017 is supervised by Smith, Jayson. If there are two Smith, Jayson's in the company, how does one determine which one supervises #10017.

By default all the solutions are assuming that there is only one Smith, Jayson in the complete database.

 

[Ruddles]

The OP shows that Employee # 10017 is supervised by Smith, Jayson. If there are two Smith, Jayson's in the company, how does one determine which one supervises #10017.

By default all the solutions are assuming that there is only one Smith, Jayson in the complete database.

My solution doesn't look at the names at all. It sees that 10017 is supervised by 6996, then sees that 6996 is s/v by 62020, then that 62020 is s/v by 64577, 64577 is s/v by 53924. When the lookup fails, a zero falls out, the trail stops and the personnel number is extracted.

Look at the VLOOKUP: in column F it looks for column A in table A:E and extracts column E, then in column G it looks for column F in A:E and returns column E, then in column H it looks for column G in table A:e... etc. It always looks for which personnel number supervised which personnel number.

It doesn't use names at all until the very end when the trail ends, a personnel number is extracted and that's used to look up the name.

 

[nldstorm]

Ive got it to work. Indeed it takes a while to run. I had forgotten to insert the final supervisors with a blank supervisor # (i.e., the zero value). Is there anything else that you can think off I can use instead of creating the additional columns? As you noted I need to be carefull with the different levels of supervisors. Withother words on certain ocasions I may have more than five levels. If not I just need to adjust the formulas. Not a big deal but it would be great if there is even an easier way.

Thanks again for the help!

 

[Ruddles]

MS Access will do this sort of thing and probably a lot faster than Excel. Otherwise I think the only other alternative is a solution in VBA, probably using a recursive routine to follow the chain of relationships, but I imagine that will be even slower. I would opt for an Access solution if it were up to me.

As regards the Excel sheet, if you have more than five levels of supervisor, merely insert a new column before column K and copy column J into it.

 

[Ruddles]

VBA solution using recursive search: place this in your worksheet code window, backup your worksheet, then run Zzip:-

Option Explicit
 
Dim iLimit As Long
 
Public Sub Zzip()
 
  Dim iPtr As Long
  
  Columns("F").ClearContents
 
  iLimit = Cells(Rows.Count, 1).End(xlUp).Row
  
  For iPtr = 2 To iLimit
    Cells(iPtr, 6) = findsuper(Cells(iPtr, 5))
  Next iPtr
  
End Sub
 
Private Function findsuper(pNumber As Long) As Long
 
  Dim fPtr As Long
 
  fPtr = 2
 
  Do Until Cells(fPtr, 1) = pNumber
    fPtr = fPtr + 1
  Loop
 
  If Val(Cells(fPtr, 5)) = 0 Then
    findsuper = Cells(fPtr, 1)
  Else
    findsuper = findsuper(Cells(fPtr, 5))
  End If
 
End Function

© Minimalist Coding, Inc. 2010

 

[Peter_SSs]

© Minimalist Coding, Inc. 2010

?



Another possible UDF approach. Code pasted in a standard module and formula in worksheet copied down. Assumption is that the data is laid out in 5 columns as shown in the original post.

<font face=Courier New><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> FinalSup(rEmp <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rEmployees <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 = Intersect(ActiveSheet.UsedRange, rEmp.EntireColumn)<br>    <SPAN style="color:#00007F">Set</SPAN> rSup = rEmp.Offset(, 4)<br>    <SPAN style="color:#00007F">Set</SPAN> rTemp = rEmp<br>    <br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> rSup.Value = "" 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(, 4)<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>        FinalSup = "Not Found"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        FinalSup = rSup.Offset(, -3).Value & ", " & rSup.Offset(, -2).Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br></FONT>

Excel Workbook
	A	B	C	D	E	F
1	Personnel number:	Last name:	First name:	Supervisor:	Spv pers num:	Final Supervisor
2	10017	Clark	Leontyne		6996	Smith, Ken
3	6996	Smith	Jayson		8888	Smith, Ken
4	62020	Bush	George		9999	Not Found
5	64577	Brown	Bobby		53924	Bell, Alexander
6	53924	Bell	Alexander			Bell, Alexander
7	8888	Smith	Ken			Smith, Ken
8	18564	Brown	Tom		64577	Bell, Alexander
9
Final Supervisor

 

[nldstorm]

Thanks everybody. Ive got it to work.