Attached is a mock up excel of my data,
The excel has a list of all employees, they are in the column “user”, then there are the managers to the right in a new column. At the very far right of everyone is “Bobby Buns”. He is the CEO of the company. Depending on your level at the company, Bobby may be 5 up from you or 1 up from you. Everyone who reports into Bobby is referred to in our company as the SET members.
I want to merge all the manager columns together with a special delimiter, then simply to a split by delimiter (far right) to pull out the CEO, then do another split by delimiter and pull out the SET members (people who report into Bobby). The issue i keep getting is that on the people who are only 1 or 2 away from Bobby they end up having multiple characters at the end of their name so i can’t do a simple split by far right delimiter, i need to add some additional logic and i can’t think of a clean way of doing it.
Executive Minus 1.xlsx (16.9 KB)