Combining similar fields into one in a pivot table or ?

SWLee

New Member
Joined
Oct 17, 2013
Messages
11
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top