Today's Excel Help

Home Forum Chat Forum Today's Excel Help

Viewing 16 posts - 1 through 16 (of 16 total)
  • Today's Excel Help
  • Premier Icon njee20
    Subscriber

    Edit, sorry, got it. Dunno.

    Premier Icon db
    Subscriber

    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

    damitamit
    Member

    you haven’t described col 1 and 2.

    Premier Icon piedi di formaggio
    Subscriber

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

    Premier Icon Rubber_Buccaneer
    Subscriber

    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?

    Premier Icon db
    Subscriber

    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

    shogger
    Member

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

    Premier Icon Rubber_Buccaneer
    Subscriber

    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.

    mossimus
    Member

    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?

    Premier Icon db
    Subscriber

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

    Makes perfect sense to me 😀

    AlasdairMc
    Member

    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?

    Premier Icon db
    Subscriber

    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 😳

    tinribz
    Member

    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.

    Premier Icon njee20
    Subscriber

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

    AlasdairMc
    Member

    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?

Viewing 16 posts - 1 through 16 (of 16 total)

The topic ‘Today's Excel Help’ is closed to new replies.