Generating value based on hierarchy

[GregDz11]

I've got a tough one, been racking my brains on this for a bit. In short I'm trying to create a "hierarchy" key based on everyone above an individual. For example if I'm four levels down from the CEO, my hierarchy key will be my id, my bosses id, his bosses id, and the CEO.

The CEO doesn't have a supervisor and will have a "level of 1", Created a google sheet with an example of what the data would look like and the output: https://docs.google.com/spreadsheets/d/1UyLF277Lb1gJ4Q0rd6mbrPVq87tMbx7hchITEoBSYp4/edit?usp=sharing

Any help would be appreciated!

 

[bobsan42]

I cant give you immediately a single formula solution, just a way around it.
I would actually sort the HierarchyID key in reverse - top level first to the left. I prefer using helper columns to do a task quickly than waste too much time thinking of a complicated formula and try to make it work.
So if you agree with this here is how it can be done: (this is for having 10 levels hierarchy but it can very quickly be adjusted to cover more)
- in D2 put this formula (and fill down along the table length):

=TEXTJOIN("-",TRUE,E2:N2)

- in E1 to N1 put the numbers from 1 to 10 (if you need more levels go further right and adjust the formula in column D)
- in E2 put this formula:

=IF($C:$C<$1:$1,"",IF($C:$C=$1:$1,$A:$A,VLOOKUP(F:F,$A:$B,2,0)))

then fill it to the right to N2 (or further if needed)
Then Fill the formula in E2:N2 down along the table.

Hope this helps.

A remark: depending on your Excel version you may have to change some references if you get a REF error, e.g.
$C:$C to $C2
$1:$1 to E$1
F:F to F2

 

[MickG]

Try this for results in "C & D"

[COLOR="Navy"]Sub[/COLOR] MG27Mar02
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Oval [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Dn.Value) = Dn.Offset(, 1): [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Oval = Dn.Offset(, 1).Value
            [COLOR="Navy"]If[/COLOR] .exists(Oval) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] .exists(Oval)
                    c = c + 1
                    nStr = nStr & IIf(nStr = "", Oval, "-" & Oval)
                    Oval = .Item(Oval)
                [COLOR="Navy"]Loop[/COLOR]
Dn.Offset(, 2) = c + 1
Dn.Offset(, 3) = Dn & "-" & nStr: nStr = "": c = 0
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[GregDz11]

@BobSan42 This is how I started, just using multiple vlookups but it seemed to bog down the sheet- the real data has approximately ~15k rows

 

[GregDz11]

Try this for results in "C & D"

[COLOR=Navy]Sub[/COLOR] MG27Mar02
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Oval [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] nStr [COLOR=Navy]As[/COLOR] [COLOR=Navy]String,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng: .Item(Dn.Value) = Dn.Offset(, 1): [COLOR=Navy]Next[/COLOR] Dn
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        Oval = Dn.Offset(, 1).Value
            [COLOR=Navy]If[/COLOR] .exists(Oval) [COLOR=Navy]Then[/COLOR]
                [COLOR=Navy]Do[/COLOR] [COLOR=Navy]While[/COLOR] .exists(Oval)
                    c = c + 1
                    nStr = nStr & IIf(nStr = "", Oval, "-" & Oval)
                    Oval = .Item(Oval)
                [COLOR=Navy]Loop[/COLOR]
Dn.Offset(, 2) = c + 1
Dn.Offset(, 3) = Dn & "-" & nStr: nStr = "": c = 0
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks mick, added this to a module but receiving the following error ="Compile error, expected end with" and highlights the end sub

Also-- I do have the value in column C for hierarchy level if that helps make the loops faster/more precise.

 

[bobsan42]

You could try - 15K is not that much It only has to work once then copy/paste the values and you're done
About the compile error: just put End With on a line between Next Dn and End Sub