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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

GregDz11

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

GregDz11

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

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,547
Messages
5,596,782
Members
414,101
Latest member
ExcelBasicBro

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
Top