Today's Excel Help
A little Excel question – Say I have 2 columns of text cells and I need in a third column all the people in a hierarchy. So in column 3 I want to flag (true or false) all the people who report to x at any level.
So in the data below if I set a cell to ‘x’ column 3 would change as listed.
a reports to e (column 3 would say False)
b reports to d (column 3 would say True)
c reports to b (column 3 would say True)
d reports to x (column 3 would say True)
e reports to y (column 3 would say False)
f reports to g (column 3 would say False)
g reports to e (column 3 would say False)
I guess some kind of lookup or index match thing could do it but I’m struggling.
many thanks if anybody can steer me in the right direction (Google is not helping!)
dbPosted 5 years agoRubber_BuccaneerSubscriber
I need in a third column all the people in a hierarchy
in column 3 I want to flag (true or false)
That’s a little confusing. I sort of think I may know what you are after. How do you know who reports to who? Is that information in a table and what does it look like?Posted 5 years ago
Thank you for the reply’s and sorry for my explaining
Col 1 contains list of Alphanumeric codes. Col 2 is the next code in the hierarchy. (I only have 2 columns and I want to add a third which I can then use auto filter on to select a subset of the lines)
The data is kind of like an org chart. At the top level there are a couple of hundred ‘master’ codes (there are a few thousand rows).
I want to pick a master code and see all the lines which report directly AND indirectly to it. e.g. they report to a code which reports to the the master code.
I don’t know how many layers the structure/hierarchy has.
Logic tells me I first need to identify the lines which report to the master code. e.g. 10 lines.
I then need to identify the lines which report to any one of the 10 lines in step 1. e.g. 900
then continue until I reach the bottom of the hierarchy
Does this help at all?
Every formula I try seems to result in some kind of circular ref.
Laptop will be going out the window soon 🙁
thank youPosted 5 years agotinribzMember
You can do it with vlookup if there were unique values in the manager column but i suspect there aren’t so vlookup would only return a list of the first matches. You would get a list of manager and everyone who worked under them at each level, but only the first one at each level.
So if there was only say a max of 3 staff working for one person you could do some complicated sorting and repeat 3 times then join all but it would be a real faff if someone does have lots of staff.Posted 5 years agoAlasdairMcMember
What I personally build for this is a big table, where you work left to right from your bottom level to your top. You can do this by doing a vlookup on the child to return the parent, then a vlookup on that result and so on. Once you’ve done that, Pastespecial Values on the lot – I’ve done this for a sheet with over a million of these so it can be quite resource hungry!!
Once you’ve done that, an hlookup on each line referencing your search value would return just the lines with that value in them anywhere.
Does that help?Posted 5 years ago
The topic ‘Today's Excel Help’ is closed to new replies.