Okay, this one isn't actually an Excel tool, and from what I've read I picked a pretty out-dated programming language to write this in. I originally wrote this in Python, but didn't share it because I knew not everyone had Python on their machines. Not being familiar with a lot of my other options, the project was shelved for nearly a year until an unrelated article lead me to Visual Basic - which works fine for my purposes.
Anyway, back to the file... It was inspired after my former boss told us about the Pomodoro technique for cycling between times of work and taking breaks. I wanted something more customizable, so I built this. When you first open the file, there are 3 prompts.
There isn't an OFF switch, so if you decide to kill it, you'll need to do so from the Task Manager.
I'll need to post a video for how to use this file, but for now I can still share it and talk through how it works.
Start by pasting your data into A:E; Employee ID, Employee Name, a Group label for your data, then Manager ID, and lastly Manager Name. Then you'll enter your most senior leader into G1. This is the employee who all other employees should roll through.
After you click "Generate Hierarchy" the file will prompt you to select which group you're generating a hierarchy for. Typing 1 will generate the hierarchy for the entire population, but the option is there to only generate the hierarchy for employees within a specific group. The group option is faster - especially when we're talking about organizations with tens of thousands of employees. 99% of the time I use option 1 here.
When the file finishes, you'll have 2 new tabs; PIVOT and Hierarchy. The hierarchy tab is where we've looped through 20 levels of supervisor reporting relationships showing who reports to who up through your organization. Column AT is the "Status" which identifies "Infinite Loops" (employees reporting to themselves or lower level direct reports - causing these loops) and "Dead Ends" which is an manager who isn't on your list - often a terminated employee. Either of these reasons will cause the hierarchy to not reach the top person you identified. Anyone else is labeled as "Okay".
The remaining columns build the hierarchy structure to make it work nicely with the pivot table. M0 is the leader you identified. M1 is their direct employees. M2 is 2-levels down...
The PIVOT tab is where we roll up the hierarchy. If you add a COUNT of Employee Name, you can easily see how many people report to who at each level of your organization, or perhaps you add salary data and create SUM of Salary to show the total salary base rolling up to each leader through the organization.
Play with the data to determine what's meaningful for you and your organization, but this file should save you loads of time having to manually build all of these formulas.
In custom files, we can generate the hierarchy as a part of a larger process and identify which employees roll up through which leaders, or we can breakout data so that managers at a certain level are able to view all direct and indirect reports. The possibilities are limitless, and it's always fun to find creative solutions to help achieve whatever the end goal is.
This file goes hand-in-hand with some of the other functionality discussed. If we're using our encryption / decryption logic, we need a simple way to distribute the unique PINs to the various users. That's where this file comes it. You only need to populate 2 columns, and the email generated is very generic - but it does the trick.
What makes this file unable to work cohesively with the Breakout Data file is the fact that I do now know what naming convention you've chosen for your Breakout files. We can update the code to auto-attach your file, but that require some additional work. Rather than having your populate hundreds or thousands of columns manually in this file, it's far simpler to simply coordinate with us on the front-end. We can capture the file names used in the Breakout Data file, and populate the specific attachment names in this file for a more seamless process. It's also completely possible to customize the email and make it more specific to your individual process.
Please reach out for any additional functionality. In the meanwhile, I hope you find this file serves its purpose and saves you some time.
The idea for this one came about after spending an afternoon breaking our master table down into dozens of smaller files for distribution, and then having to redo the whole project after being told to include additional columns.
This tool is pretty straight forward. To use it, simply paste your data into the Data tab and click a button. The file will read your headers and you'll be prompted to select which header you wish to breakout the data using. Enter the corresponding column and click OK.
Let's assume you're breaking out an employee population by manager, so you'd enter the column which contains the manager ID. You then go grab some coffee. The file will generate a file for each manager ID and save each file onto your Desktop - they may also be saved to your Documents folder or your most recent working folder - for whatever reason, it doesn't always put the files where it's told.
The only real headache with this tool is that you still have to distribute all of the files to the appropriate manager, and this file doesn't support a unique password for each file. This can certainly be done. Feel free to reach out if you need additional functionality to support your needs.