TheRedCardinal
Active Member
- Joined
- Jul 11, 2019
- Messages
- 250
- Office Version
- 365
- 2021
- Platform
- Windows
I have a sheet (Sheet 1) with a column that contains numerical employee IDs. Each employee has a single row.
I have another sheet (Sheet 2) that lists those IDs and contains project supervisors. Some employees have more than one supervisor. Where this is the case, the employee is listed in multiple (successive) rows, with the same ID, but the name of the supervisor varying.
I need to create 4 columns (that is the maximum number of supervisors) in Sheet 1, and lookup the names of all possible supervisors, sometimes 1, sometimes as many as 4.
I have some success with this by doing the following:
I have written VBA code to do this but the user is not keen on it and would prefer a formula for future use.
Can this be done?
I have another sheet (Sheet 2) that lists those IDs and contains project supervisors. Some employees have more than one supervisor. Where this is the case, the employee is listed in multiple (successive) rows, with the same ID, but the name of the supervisor varying.
I need to create 4 columns (that is the maximum number of supervisors) in Sheet 1, and lookup the names of all possible supervisors, sometimes 1, sometimes as many as 4.
I have some success with this by doing the following:
- Creating a "helper column" in sheet 2 that uses a simple rolling COUNTIF formula to add an appending -1, -2, -3 or -4 to the employee ID
- Calling my column headers Supervisor 1, Supervisor 2 etc in Sheet 1
- Using Vlookup to lookup [ EmployeeID & "-" & Right(ColumnHeader,1)] in Sheet 2
I have written VBA code to do this but the user is not keen on it and would prefer a formula for future use.
Can this be done?