Distinct/Unique Value Hierarchy Restructure

[Mothergreen]

Good Morning All

I have been trying to figure a way to adjust a spreadsheet. Its gotten to the point where i don't know if i am going down the most effective route or not and i hit a road block.

Problem
imagine if you will you have a spreadsheet columns A, G, H, I have names of people in them. Names in A work for G, G works for H, and H works for I. Names in this list can appear in more then one column so when you filter in a specific column it showing the next person higher up. (Basically a floating hierarchy). This is no good. I need to move this information to a new sheet and develop the hierarchy copying the rows with it. So i decided to work backwards since i know the name at the top of the list.

Please Login or Register  to view this content.

The other method i have been working with is an autofilter method to fill in the fields. The code is a bit different but it gets the same results. But at this point i hit a roadblock. I have my starting point John Doe (no one is higher) code starts everything off great. I just can't figure out a way for it to continue with the next group of unique values in column I from Sheet 1.

In theory, at least, we use the data sheet to start everything off, data transfers to sheet 1 and adds a column in before G shifting everything over giving us the next group of values in column I, if this continues to loop it should bring us to the end of the hierarchy or at least as far as column I can take us which is fine. I think with this idea there could be an issue with duplicates but instead of a copy a move would possibly eliminate this from occurring.

I don't know if this is a sound idea or if i just lost my mind and how i can pull this off.

P.S. Any help will be great. Thank you in advance to all those that just took the time to read this. And as always thank you everyone for all your hard work that you do on this forum.

[shg]

There's a workbook at https://app.box.com/s/5ox0b5u4vrm8r2uorxe8grh79w0b4x9u that may do what you want.

[Mothergreen]

Thank you SHG for the quick reply

The report i am working with unfortunately has the top person listed more then once in column I because there are 6 people that report directly to him in column H. Also the hierarchy can shift at times were people that were at Level 1 can go to Level 2 be under one person one week and be under another team the next.

This is why i had to go this route with the code and why i am trying to figure out how to have it continuously read column I as the columns shift until i run out of names in that column to work with. Another thing i was just recently informed is that there might be multiple spreadsheets similar to this just for different individuals in the hierarchy. But that wont be a problem really because it will always indicate who the top person is for that report and that can be inserted into the code directly to start things off.

I will keep looking at the information that you sent me, i might be missing something that might be obvious just at this point the Excel grid lines are burnt into my retinas.

[shg]

the top person listed more then once in column I because there are 6 people that report directly to him in column H

In the workbook I posted, each employee must appear at least once in col C, because each must report to someone, and can appear only once because they can report to only a single person. The same employee can appear multiple times in col E as the person the whom the employees in col C report.

[Mothergreen]

Thank you SHQ. I was able to get this to work but i have to do a lot of manipulating to the original report to get it to work. The reports that have boss at the top worked, but the reports that have anyone but the boss requires for me to go into the report and remove the people above them from column H and I since the people above them are not listed not listed in column A. I will continue see what i can do to make this process smoother.

I still though would like to know if there is a way to have the code up top continue its loop in column I on sheet 1 when new information is copied over. Thank you again for your help.

[shg]

I'm going to punt on this; it's not code I'm interested in revisiting.

Perhaps someone else here can help gratis, or there is a commercial services forum here when you can pay a modest price for assistance. In the latter case, anyone is welcome to use my code (or yours, of course) as a point of departure.

Good luck.

[Mothergreen]

my apologies if my request requires paid assistance. If i happen to resolve this or find the solution i will make sure to post it. I will keep continue to plug away at this. Thank you for your help.

[shg]

my apologies if my request requires paid assistance

It doesn't require it, just wanted to make you aware of the option.