working excel: how to find employees, reporting to someone in a flat and non-descript hierarchy

Excel got me excited today. This rarely happens so I thought I would share my excitement and my learnings about this great and simple data crunching tool.

The Problem

It's actually not so hard to explain: Imagine you have an spreadsheet list of employees (so far so good) and in columns further down the hierarchical reporting line of these employees is shown. So in the very simple example (filled with totally randomly generated data!) - column A shows the employees email address and column B the direct boss. Column C would contain his boss's boss and so on.As outlined in the above picture the relationships between the bosses levels are non-descript. Meaning that boss "Jon Doe" can appear on the first boss level or on the third and so on. I.e. it's not clear from the data how the hierarchy works in every case it is just given in these columns on an per employee basis.Now the core of the problem would be: Imagine you would be given a list of 5 bosses. Now we need to develop a nice and efficient report on this spreadsheet data, which shows all employees reporting in any hierarchical way (i.e. through bosses in the middle) to these 5 bosses. How do we do that (again remember - each of these 5 bosses could show up anywhere in column B, C or D)?

Building Blocks of the Solution

• First of all I'd like to mention once again: This is sample data (i.e. I went to a place called randomlists.com and generated data)
• I inserted a real table into the Excel spreadsheet (this makes relative cell links more readable and so on)
• In order to make later steps like the pivot step work nicely I've inserted an artificial name counter (a column filled with 1)

Key steps to build the solution:

• Do a string addition of all the bosses (just add up the boss-strings to one bigger string)  (my formula: =[@BossLv1]&","&[@BossLv2]&","&[@BossLv3] )
• Make sure you can search for the relevant bosses in the hierarchy. I did this with a kind of parameter and an if clause that marks the boss I'm looking for (again this may seem strange but it helps in one of the next steps when it comes to the pivot)  (my formula:   =IF(ISNUMBER(FIND($I$3;[@AddedString];1));"Analysis Found";"no")  )
  • Note that in the above you need to catch the number with ISNUMBER otherwise the find function doesn't help if something is not found.
• Insert a Pivot. Define the Row data to show the added string of bosses and the users that show up in these string-added-hierarchies. Set a parameter on top of it to only show the boss you're looking for (AnalysisFound flag is the parameter). And Add up the counter field in the Value section of your pivot.
• Voila that's it: and if you now change your analysis boss and refresh the pivot it shows you exactly how many users are attached to a particular boss's hierarchy

I've attached my sample:  Test-pivot01 :-)