Singletrack Mountain Bike Magazine

• Overview
• Bike Forum
• Chat Forum
• For Sale
• Wanted
• Forum Help
• Classified Rules

Chat Forum

1. db - Member

   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

   Posted 1 year ago #
2. njee20 - Member

   Edit, sorry, got it. Dunno.

   Posted 1 year ago #
3. damitamit - Member

   you haven't described col 1 and 2.

   Posted 1 year ago #
4. piedi di formaggio - Member

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

   Posted 1 year ago #
5. Rubber_Buccaneer - Member

   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 1 year ago #
6. db - Member

   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

   Posted 1 year ago #
7. shogger - Member

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

   Posted 1 year ago #
8. Rubber_Buccaneer - Member

   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.

   Posted 1 year ago #
9. 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?

   Posted 1 year ago #
10. bigjim - Member

    

    Posted 1 year ago #
11. db - Member

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

    Makes perfect sense to me

    Posted 1 year ago #
12. 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?

    Posted 1 year ago #
13. db - Member

    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

    Posted 1 year ago #
14. 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.

    Posted 1 year ago #
15. njee20 - Member

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

    Posted 1 year ago #
16. 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?

    Posted 1 year ago #

RSS feed for this topic

Topic Closed

This topic has been closed to new replies.