Pivot equation - unique pairs

[guscrouchend]

Hi,

I have 4000 records of tennis matches. Player names are in columns A (home) and B (away)

In my pivot table my colmn labels are home players. I would like the data to be a list of all away players that the home player has played.

The problem is that I have no idea where to start.

Any help would be much appreciated.

Gus

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Match result</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">PT - Layout</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Home</td><td style=";">Away</td><td style=";">Sum of Home-R</td><td style=";">Sum of Away-R</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Home - Row</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Player100</td><td style=";">Player142</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Away - Row</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">Player294</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Home-R - Data</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">Player374</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">Away-R - Data</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">Player530</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Player101</td><td style=";">Player149</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">Player192</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">Player296</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">Player357</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">Player507</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style=";">Player792</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style=";">Player826</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Player102</td><td style=";">Player401</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style=";">Player501</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style=";">Player506</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style=";">Player705</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style=";">Player890</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">TDP</p><br /><br />
Markmzz

 

[guscrouchend]

Thanks for your reply.

Apologies orry but i'm quite new with PTs. How could I acheive that?

Could I potentially get the home player's name in col A and then the away players in the other columns (displayed horizontally)?

 

[markmzz]

Thanks for your reply.

Apologies orry but i'm quite new with PTs. How could I acheive that?

Could I potentially get the home player's name in col A and then the away players in the other columns (displayed horizontally)?

Guscrouchend,

Sorry, but I don't know how to do that using the capabilities Excel PivotTable. I will continue researching and if I find how to do that I will post here.

Now, let's wait for another user.

Markmzz