Repeat VLOOKUP a variable number of times

[shawnw]

I am looking for a formula....

I have one worksheet which contains the following columns:

CHARTER HIERARCHY
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]Title[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Owner[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Parent Title[/TD]
[TD="align: center"]Strategy[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]001[/TD]
[TD]Project[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Golf[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD]002[/TD]
[TD]Program[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Delta[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]003[/TD]
[TD]Project[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Bravo[/TD]
[TD]Strat 2[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]004[/TD]
[TD]Charter[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD]Strat 2[/TD]
[/TR]
[TR]
[TD]Echo[/TD]
[TD]005[/TD]
[TD]Project[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD]Golf[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Foxtrot[/TD]
[TD]006[/TD]
[TD]Charter[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD]Strat 3[/TD]
[/TR]
[TR]
[TD]Golf[/TD]
[TD]007[/TD]
[TD]Program[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD]Foxtrot[/TD]
[TD]Strat 2[/TD]
[/TR]
</tbody>[/TABLE]

This worksheet pulls data from another system. It is essentially a flat file version of a relational database. As you can see, each project's parent is a program and each program's parent is a charter. Charters do not have parents.

• Alpha (project) rolls up into Golf (program) which rolls up into Foxtrot (charter).
• Charlie (project) rolls up into Bravo (program) which rolls up into Delta (charter).

I have a second worksheet which contains the following columns:

PROJECT DETAILS
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Title[/TD]
[TD="align: center"]Charter Lookup[/TD]
[TD="align: center"]Capitalizable[/TD]
[TD="align: center"]In Service Date[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]formula goes here[/TD]
[TD]Yes[/TD]
[TD]Jan 1[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]formula goes here[/TD]
[TD]No[/TD]
[TD]Feb 1[/TD]
[/TR]
[TR]
[TD]Echo[/TD]
[TD]formula goes here[/TD]
[TD]Yes[/TD]
[TD]Jan 15[/TD]
[/TR]
</tbody>[/TABLE]

The "Title" column refers to the same data in both worksheets.

There is a legitimate reason that this data is in two separate worksheets, as opposed to just added "Capitalizable" and "In Service Date" to the CHARTER HIERARCHY worksheet.

In the Project Details worksheet there is a column called "Charter Lookup" (Col B). What I am trying to do is a VLOOKUP from the Charter Hierarchy worksheet to find the parent of the project. But I want to go all the way to the charter level, skipping projects and programs.

For example:
=VLookup("Alpha",CHARTER HIERARCHY, 6)

This should find "Golf" in the Charter Hierarchy worksheet. The problem is that Golf is not a charter. It's a program. So now I need to do another VLookup on the same table, like this:

=VLookup("Golf",CHARTER HIERARCHY, 6)

This should return "Foxtrot," which is a charter. So now I want the cell value in the Charter Lookup field to equate to "Foxtrot."

Basically, I need to keep polling the table until I find a result of type Charter. Then I need to return that result. If there is not result of type charter, then I can return "No Charter Found."

I hope this makes sense.

Thanks,

Shawn

 

[shawnw]

Peter,

First, I want to thank you again for your time. I've taken more of it than I would have liked, owing to suboptimal decisions on my part. So you have my thanks and my apologies.

I thought I was doing everyone a favor by creating a sample table with dummy data. For what it's worth, that also seemed more practical than entering 390+ rows of data in Mr. Excel. I don't even know how to do that in any practical way. It's hard enough to create small tables.

Each week the table grows. It will soon be 400 or 450 rows. That is why I tried to use a named range instead of $A$2:$H$393, for example. One side benefit of the named range is that typos, such as 392 instead of 393, can be avoided.

I referred to "Portfolio Item" as "Parent Name" in the sample data, but they are the 100% same thing. The portfolio item is the next node up on the hierarchy; the parent node. Rather than explain that concept here, I just called the column "Parent Name," which implied it. That seemed to make it easier. If I were totally true to the data, this would not even be called "Epics." It would be called "Portfolio Items" and each Portfolio Item would have another Portfolio Item as its parent. You can seen how that could become confusing. Or, at least, you can see how I might imagine it could be confusing and might seek to avoid that confusion by renaming column H to something more intuitive.

Yes, the data in column H ("Portfolio Item" or "Parent Name") repeats. I thought that didn't need to be said, and apologize for my mistake. Just as a human parent can have many children, a Portfolio Item can have many sub-nodes. For example, one charter may have N programs and N projects and N features. Importantly - While there many be many circuitous routes up the hierarchy, IT IS NOT POSSIBLE TO FIND MORE THAN ONE CHARTER AT THE TOP OF THE HIERARCHY.

To be candid, you hold all the chips here. You have the expertise and I am struggling. I need your help, and - frankly - my job may now depend on it. Please let me know what else I can provide.

Many thanks

 

[Peter_SSs]

I think that I may not have stated clearly enough what I was trying to say in post #30 & I may need to re-visit some of those points later. However, in reviewing the thread & my code I note that I have posted a wrong version of the code and that will likely be at least one reason many of the function results are incorrect.

Please insert this blue line where shown and try again

sTmp = sTitle
i = 0
Do

In cell M2 you should be able to use either

=Charter(C2,$A$2:$H$393)

or

=Charter([@Title],Epics[[ID]:[Portfolio Item]])

 

[shawnw]

Thank you for the update. Again, I appreciate your time. Mixed results...

Good News -- The formula/function no longer throws an error - and works correctly for almost all epics. That's a big step in the right direction and I can finally see light at the end of the tunnel.

Bad News -- However, there are some unexpected/incorrect results. To do a sort of double-check, I added two columns next to your function in Column M.

1. Column N = AND([@Type]="Charter",[@ID]=[@CHARTERID])
   1. This should return TRUE whenever the epic is a charter and the Charter ID returned by your formula (in Col M) returns the same value as in Col A.
2. Column O = INDEX(B:B,MATCH([@CHARTERID],A:A))
   1. This should return the word "Charter" when the CharterID is, in fact, an epic of type charter.

I discovered that in some cases the formula is returning "Project (< 3 mo)" instead of actually finding the charter. Not sure where it's breaking down. Maybe this is related to your comment about epic names being repeated, but I can't know for sure.

I updated the XLS here in the hope that it will help.
https://drive.google.com/open?id=0B7CDe77cNqwNVEtxc2VPVnhXcm8

I look forward to your feedback

 

[Peter_SSs]

From your description, extra formulas & a fairly quick look, I can see one example of a wrong result: row 186.
Yes, this is related to repeated items in column C. "Generator 2.0 Rollout" is not only in C186, but also C255, and that is what is causing the function to return an incorrect result.

1. Are there other incorrect results? If so, could you please identify the row(s)?

2. Are you able to sort the data in a different order? If so, try sorting column B in the order 'Z to A', so all the Charter items are last. If you do that I think the particular error for "Generator 2.0 Rollout" should be corrected, but I am unsure if other problems would arise. I'll leave you to check & identify.

 

[shawnw]

Peter -

The "error" scenario you point out is common. Actually, it's not really an error. A single charter may spawn one or more programs or projects. Put another way, multiple features, projects, programs - or a mix thereof - can roll-up into a single charter. Is it possible to allow for this without an error? By sorting the epics as instructed and by changing the MATCH parameter to zero (exact match), I can avoid this error. It's a viable workaround if the code cannot be made to avoid the problem. I just have to remember to re-sort the data every time a new epic is added to the table, which introduces the risk of error.

ENHANCEMENT
It occurs to me that it would be useful to handle errors differently. Specifically, rather than error out with "orphaned project" (or feature or program), it would be better to return the ID of the highest node on the hierarchy related to the current row. For example, maybe a feature links to a project and then a program, but not a charter. In this case, it would be good to return the ID of the program. For example, maybe a feature has no parent at all. In this case, return that feature's ID. With this approach, I use the ID to lookup the type. If the type is not equal to charter, then I know there was an orphan and lose nothing. But I gain the ability to group items not only by charter, but by ANY type.

I can try to do this myself, but wonder if you can point me in the right direction within your code. This is what I did:

Function GetCharter(sTitle As String, rTable As Range) As String
Dim d1 As Object, d2 As Object
Dim a As Variant
Dim i As Long
Dim sTmp As String, sLast As String

Const lIDCol As Long = 1 '<-Column within data table containing ID
Const lTypecol As Long = 2 '<-Column within data table containing Type
Const lTitleCol As Long = 3 '<-Column within data table containing Title
Const lParentCol As Long = 7 '<-Column within data table containing Parent Name
Const MaxLoops As Long = 50 '<-Safety to ensure infinite loop doesn't occur

Set d1 = CreateObject("Scripting.Dictionary")
d1.CompareMode = 1
Set d2 = CreateObject("Scripting.Dictionary")
d2.CompareMode = 1
a = rTable.Value

For i = 1 To UBound(a)
d1(a(i, lTitleCol)) = a(i, lIDCol) & "|" & IIf(a(i, lTypecol) = Empty, "NA", a(i, lTypecol))
d2(a(i, lTitleCol)) = a(i, lParentCol)
Next i

sTmp = sTitle
i = 0
Do
i = i + 1
sLast = sTmp
sTmp = d2(sTmp)
Loop While d2.exists(sTmp) And i < MaxLoops


'COMMENTED OUT THE IF STATEMENT TO ALWAYS RETURN A RESULT
'If i = MaxLoops Or Split(d1(sLast), "|")(1) <> "Charter" Then
' GetCharter = "Error - Orphaned " & Split(d1(sTitle), "|")(1)
'Else
GetCharter = Split(d1(sLast), "|")(0)
'End If


End Function



Updated XLS is here: https://drive.google.com/open?id=0B7CDe77cNqwNdDNRaWxPWUYyYUU

BTW - Excel runs VERY slowly when this formula is calculating. Is there a way to speed it up? Again, I'm willing to investigate/try if you can make a general suggestion.

Thanks,

Shawn

 

[shawnw]

Performance is getting worse. It grinds Excel to a halt as all processors are used. The more data I add, the worse it gets. To mitigate, I want to change this to a sub routine that only fires when I click a button. Here is what I have so far...

>>>>THIS IS THE FUNCTION THAT RETURNS THE CHARTER ID
>>>>It works correctly as a function, but fails when I call from a subroutine. I presuming there is a problem with the range I'm passing.

Function GetCharter(sTitle As String, rTable As Range) As String

Dim d1 As Object, d2 As Object
Dim a As Variant
Dim i As Long
Dim sTmp As String, sLast As String

Const lIDCol As Long = 1 '<-Column within data table containing ID
Const lTypecol As Long = 2 '<-Column within data table containing Type
Const lTitleCol As Long = 3 '<-Column within data table containing Title
Const lParentCol As Long = 6 '<-Column within data table containing Parent Name
Const MaxLoops As Long = 10 '<-Safety to ensure infinite loop doesn't occur

Set d1 = CreateObject("Scripting.Dictionary")
d1.CompareMode = 1
Set d2 = CreateObject("Scripting.Dictionary")
d2.CompareMode = 1
a = rTable.Value

For i = 1 To UBound(a)
d1(a(i, lTitleCol)) = a(i, lIDCol) & "|" & IIf(a(i, lTypecol) = Empty, "NA", a(i, lTypecol))
d2(a(i, lTitleCol)) = a(i, lParentCol)
Next i

sTmp = sTitle
i = 0
Do
i = i + 1
sLast = sTmp
sTmp = d2(sTmp)
Loop While d2.exists(sTmp) And i < MaxLoops
GetCharter = Split(d1(sLast), "|")(0)


End Function


>>>>THIS IS THE SUBROUTINE THAT calls the function
>>>> This populates the correct cells, but only inserts one value in all of them.
Sub RefreshCharters()


Dim answer As Integer
answer = MsgBox("Do you want to update the charters?", vbYesNo + vbQuestion, "Update Charters")
If answer = vbNo Then Exit Sub

'Add a visual timestamp so I see when the last refresh was
Range("L2").Select
ActiveCell.FormulaR1C1 = Format(Now(), "Mmm DD, YYYY hh:mm:ss")

Dim tbl As ListObject
Dim FirstRow As Long
Dim LastRow As Long
Dim NumToDo As Long

'Cycle through the cells that need to have values set
Set tbl = ActiveSheet.ListObjects("Epics")
FirstRow = 4 'First row of the data table after three header rows
LastRow = tbl.Range.Rows.Count + 2 'Last row of data considering header rows
NumToDo = LastRow - FirstRow

'Define range for the GetCharter function
Dim sourceTable As Range
Set sourceTable = Worksheets("Epics").Range("Epics")

'For each row in the range, populate the charter's Epic ID
Dim i As Long
For i = FirstRow To LastRow
' Insert the Charter ID into column L of every row in range
Cells(i, 12).Value = GetCharter(Cells(i, 3).Value, sourceTable)
Next i

Set tbl = Nothing
Range("L1").Select


End Sub


Thanks!