Designing a Pivot Table for the same names recurring in both of two different columns

Hi.

I a struggling with how to put together a pivot table that will analyse a given name in relation to other names. Please see the attached spreadsheet for a simplified version of the format I'm using. Essentially columns A - C are the overriding groupings, with columns D - G being specific to them and the differentiating factors between the records. Within each session, there is a pair of people (Name 1 and Name 2) working together, with their totals avchieved indicated next to their names (Total 1 and Total 2 respectively). These pairings vary considerably over the course of a session, so the names are thoroughly jumbled up amongst the two columns by the end.

I realise that it would be far more efficient from an analysis point of view to have all the names from columns D and F in just one column, with their associated totals next to them in another column. However, that would enormously complicate my data entry procedure, as that is based on the column A - C structure. As such, the same names reoccur again and again evey few rows, sometimes in column D and at other times in column F. Analysing the data for the same name in both columns is important though.

Can anyone suggest how to structure my pivot table so that I can select any names (including occurrences in both columns D and F), and see an analysis of both the column E and G figures associated with that name, as well as all the other column D and F names (and their associated column E and G totals) that share the same row as the selected name?

In short, I need to be able to select any one of those names, and then see the combined total for that name, as well as the names of any other person who shared the same row with them at any point, and the (combined) totals achieved by each of those persons.

Thanks!

Looking through my Excel Bible in the meantime, I see that the current format of my table is not appropriate for pivot table usage, in as far as it does not contain normalised data. The only way that I can think of to get around this issue (it seems as though a pivot table cannot combine data from two different columns) is to make double entries for each pairing, such that each partner's name appears in both column D and column F. That would allow Excel to look up any specified name in column D, and be able to list all associated names found in column F.

Not an ideal solution from a data entry perspective, in that each pairing has to be entered twice (to give each partner his own row), so if there is an easier way please feel free to pass on suggestions!

Thanks.