What i need to do is -
1. I have a base data sheet(Data) with user id(col D), Username(col F) and Reports to(col I) data of all employees.
2. i have a set of unique managers and their ids in a sheet X.
3. In sheet Y i have a list of selected managers and their ids which are not in sheet X(dynamic count).
4. For each of the managers in Sheet X i have get the corresponding manager from the Data sheet, if the corresponding manager's name is in the list of sheet Y i have to put them in the sheet X beside the manager name column as VP name and Their Id as VP id column, otherwise i have to find the corresponding manager's manager in the Data sheet and then match it the similar way with the list in sheet Y and go on looping for managers against users from Data sheet till i reach a manager whose data matches with the one in the sheet Y.
5. There can be few data which will not loop up to any manager in the provided list in sheet Y. the scenarios as per my logic are as below:
Infinite loop conditions encountered while populating the Manager Mapping sheet and possible solution -
1. User Name = Manager Name (in data sheet)--> Stop --> Check if the user name exists in the VP list --> If yes, set as VP for the manager, else "Mgr not in VP list" dummy text should be entered in the VP name column against the manager.
2. User has a manager which is already checked for previously (may be i can have an array for each manager to store its corresponding mgr so that i can chk evreytime whether i am looping through the same names or not)--> Stop the loop --> Set "Mgr not in VP list" dummy text.
Also all the data ranges are dynamic which changes every month.
I can logically derive to this answer but i am stuck with the coding.Someone please help me with a solution.
Attaching the sample data.
Sheet X data - Unique manager list, Sheet Y data - List of Provided senior managers with which the VP column should map to and Data Sheet - Have the data of all users.
Expected Result sheet contains the result set i need to generate in Sheet X with explanations side by side.
Also, the format of names in the manager, Sheet Y and Data sheet are not same. So we need to consider the id always for finding matches.
These are dummy data and the record number is dynamic.
Please help. Its too critical for me.
Bookmarks