My spreadsheet captures information about military basic training - the trainee's name, the squadron number, the flight number, start/end dates, etc. A flight can have more than one TI (Training Instructor), so in any given record, there can be TI1, TI2, and TI3, which are listed by last names only. Most trainee's are only assigned to one flight, but any TI will be assigned multiple flights over the course of a year. For that reason, I have a separate field for TI1, TI2 and TI3. This works well for holding my data. However, I would like to use the same data to see all the Squadrons/Flights that any one TI has been in charge of. I currently do this with a complicated process, but as this spreadsheet grows, I'm afraid my process is going to prove both too cumbersome and also require a lot of resources. Does anyone have any suggestions for a simple way to accomplish the task?
1. Sample of the raw data
2. Sample of what I'm trying to be able to see
1. Sample of the raw data
2. Sample of what I'm trying to be able to see