Can a macro help create a user hierarchy?

[sinkrswim]

Hi everyone,

I'm very new to VBA, so really hoping you guys can help with this. I have a very painful that I need to do every week and I am trying to automate it to some extent to try and save some time (and my sanity!).

I get a file from HR with the email address, name, title, and email+name of the manager and second-level manager info of every employee, plus a few other columns with demographics. I have to use this data to create an organizational hierarchy in a slightly different format, so the manual steps go something like this:

Email

Display name

Job title

Reports to

Manager's email

Second level manager

1. Filter the Job title column to find all VPs and copy and paste the list into a new sheet.
2. Filter the 2nd-level manager column using the list of VPs (one by one), and copy+paste the employees who report up to the VP into the new format, which looks like this:

AGENT_ID

AGENT_NAME

SUPERVISOR_ID

SUPERVISOR_NAME

MANAGER_ID

MANAGER_NAME

DIRECTOR_ID

DIRECTOR_NAME

VP_ID

VP_NAME

3. Repeat the process for Directors, Managers, Supervisors, Team Leads, etc. until each employee is accounted for.

I don't know if a macro is even possible or useful for something like this, but crossing my fingers that there's someone out there who enjoys puzzles and has time to kill

 

[Akuini]

Could you provide a sample of the data along with the expected results?
You can share your data by using the XL2BB tool or by uploading a sample workbook (without any sensitive information) to a file-sharing site such as Dropbox.com or Google Drive. Please share the link here and ensure that it is accessible to anyone. If your data is sensitive, consider creating some dummy data instead.

 

[sinkrswim]

Could you provide a sample of the data along with the expected results?
You can share your data by using the XL2BB tool or by uploading a sample workbook (without any sensitive information) to a file-sharing site such as Dropbox.com or Google Drive. Please share the link here and ensure that it is accessible to anyone. If your data is sensitive, consider creating some dummy data instead.

Thanks for the quick reply. I've created a dummy file with two sheets - the raw data I get from HR and a sample output. The file is available here!

 

[Akuini]

I've created a dummy file with two sheets - the raw data I get from HR and a sample output. The file is available here!

Thanks, I've downloaded your file.
I'm struggling to understand each step of what you're trying to do. Could you break down the result of each step instead of only providing the final result?
I need to know the result of each step, with each on a different sheet:
1. Filter the Job Title column to find all VPs and copy and paste the list into a new sheet.
2. Filter the 2nd-level Manager column using the list of VPs (one by one), and copy+paste the employees who report up to the VP into the new format, which looks like this:
AGENT_ID | AGENT_NAME | SUPERVISOR_ID | SUPERVISOR_NAME | MANAGER_ID | MANAGER_NAME | DIRECTOR_ID | DIRECTOR_NAME | VP_ID | VP_NAME
3. Repeat the process for Directors.
4. Repeat the process for Managers.
5. Repeat the process for Supervisors.
6. Repeat the process for etc ....

 

[sinkrswim]

Thanks, I've downloaded your file.
I'm struggling to understand each step of what you're trying to do. Could you break down the result of each step instead of only providing the final result?
I need to know the result of each step, with each on a different sheet:
1. Filter the Job Title column to find all VPs and copy and paste the list into a new sheet.
2. Filter the 2nd-level Manager column using the list of VPs (one by one), and copy+paste the employees who report up to the VP into the new format, which looks like this:
AGENT_ID | AGENT_NAME | SUPERVISOR_ID | SUPERVISOR_NAME | MANAGER_ID | MANAGER_NAME | DIRECTOR_ID | DIRECTOR_NAME | VP_ID | VP_NAME
3. Repeat the process for Directors.
4. Repeat the process for Managers.
5. Repeat the process for Supervisors.
6. Repeat the process for etc ....

I use the data to create a tiered hierarchy. The top tier is called "VP" in the output system and file. So first, I need to identify all users that are at the top-most level and move them to a new sheet. Then, I have to find every other employee who reports to them or to someone who reports to them and add these to the new sheet with the name of the Supervisor, Manager, Director, and/or VP to their row.

So, in the file, if you look at the rows with Customer Experience in the Channel on the raw data sheet, there's a VP named Leah Solis. She has a Manager reporting to her, Cinnabar, who has a Supervisor reporting to him named Rosita. Rosita then has 10-11 people who report to her. The people reporting to Rosita are the first tier. Rosita is tier 2, Cinnabar tier 3, and Leah is the final tier.

Similarly, for the Commercial channel, the highest person in the hierarchy is John Doe, and reporting to him is Hattie (and others not in the dummy data). Finally, Jane Doe reports to Hattie. On the output file, I would add a row for each of them like this:

AGENT_ID	AGENT_NAME	SUPERVISOR_ID	SUPERVISOR_NAME	MANAGER_ID	MANAGER_NAME	DIRECTOR_ID	DIRECTOR_NAME	VP_ID	VP_NAME	SITE	DEPARTMENT
jane.doe@abc.com	Jane Doe					hattie.vandran@abc.com	Hattie Vandran	john.doe@abc.com	John Doe	United States	Commercial
hattie.vandran@abc.com	Hattie Vandran							john.doe@abc.com	John Doe	Canada	Commercial
john.doe@abc.com	John Doe							mars.interfector@abc.com	Mars Interfector	United States	Commercial

That's just how I've been doing it; if there's a better way, I'm totally open to trying anything that will save me time.

 

[Akuini]

Sorry, this problem is much harder than I thought . So, I hope others will step in.
I believe a solution will utilize a recursive sub. There's a thread with similar problem where @DanteAmor provided a recursive sub
to solve it, here.

 

[sinkrswim]

Sorry, this problem is much harder than I thought . So, I hope others will step in.
I believe a solution will utilize a recursive sub. There's a thread with similar problem where @DanteAmor provided a recursive sub
to solve it, here.

Thank you. I looked at the script shared by @DanteAmor but it's beyond my skill level. I guess I will just have to keep doing this work manually.

 

[Akuini]

I'll give it another try, perhaps tomorrow, though I can't promise.
Question:
Base on Job Title, how do you determine which ones are VP? Is it VP & SVP? any else?