Hi Jon,
you could create reference via lookup that matches all known variations of each role to a single title reference. For example, all entries in column titles ( CEO, chief executive officer, etc ) map to CEO in column role.
Second step would be to use the pivot transform to summarize each occurance