Folks

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!)

db

Edit, sorry, got it. Dunno.

you haven’t described col 1 and 2.

Not enough info there to really know what you want, but suspect something like a VLOOKUP would work for you

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?

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 you

Can you upload it to GOOGLE DOCS dude so we can take a look…

Laptop will be going out the window soon

Don’t chuck the laptop until I have finished beating you around the head with it! Your explanation makes about as much sense as a phonecall from Kayvan Novak.

So from your example above if you set cell to Y
Column 3 in rows 1, 5,6, 7 would be true and the rest false?

mossimus- correct. Then I can use autofilter on the last column and get the rows I need.

Makes perfect sense to me 😀

OK, so column A is the child, column B is the parent, and you want to see whether the child has a direct or indirect relationship to the parent? Like a financial hierarchy perchance?

Correct AlasdairMc – and there are multiply ‘generations’ in the data I have.
So I want to pick a single ‘parent’ and see all the generations below it.

all other analogies welcome!

and sorry for the lack of a good explanation from myself 😳

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.

You can do vlookup on array formulae to return multiple values. Google it, quicker than me typing it!

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?