Generating value based on hierarchy

GregDz11

New Member
Joined
Mar 13, 2017
Messages
14
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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):
Code:
=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:
Code:
=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
 
Last edited:
Upvote 0
Try this for results in "C & D"
Code:
[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
 
Upvote 0
@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
 
Last edited:
Upvote 0
Try this for results in "C & D"
Code:
[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.
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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
Back
Top