see duplicates in rows and columns in the text

[wabbak]

I want to do the following, but have no clue as to how I should name it! So my title is probably way off!
I have 54 people that play in groups of three. The next week I have 57 people, some the same, some new, that again play in groups of three. I want to make a table (transpose, macro, pivot??) so I can see if person A has already played with person Z, and if so, how many times. And that for all players. If it were only two rounds, I could do this manually, but this already amounts to 7 rounds and more are coming. I already have many duplicate sets of players...
Is this at all possible?

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[wabbak]

Here is an example. I have 14 (A-M) players spread out over 3 days. Not always the same players. They play in 3 to a flight. I would like to make a table that shows how many times, if any, any given player has played with any other player.

[Glenn Kennedy]

The yellow banner asked for some manually calculated results. They are not there. I do not know what you want. Please amend.

[wabbak]

I hope this helps.

[JeteMc]

This proposal restructures the data, as modeled in columns Q:S, and employs two helper columns (T:U), which may be moved and/or hidden for aesthetic purposes.
The helper columns are populated using: =INDEX($R$5:$R$31,AGGREGATE(15,6,(ROW($R$5:$R$31)-ROW($R$4))/($Q$5:$Q$31=$Q5)/($S$5:$S$31=$S5)/($R$5:$R$31<>$R5),1))
The range C17:O29 is populated using: =SUM(COUNTIFS($R$5:$R$31,$B17,$T$5:$T$31,C$16),COUNTIFS($R$5:$R$31,$B17,$U$5:$U$31,C$16))
Let us know if you have any questions.

[wabbak]

Thank you! But this doesn't help me to see at a glance that player A has played with B and C twice and F and D once. and not at all with the others. That's what I want to see. and that doesn't show in your table. Hope you can find a way...

[Glenn Kennedy]

Please indicate SPECIFIC cells where Jete's soltion does NOT give you the correct result.

[wabbak]

I'm sorry, but this is as specific as it gets. All cells give a result that I do not require. I have a given: table A. I want to translate this table so it shows me table B. So column R should show player A to M. Row 5 should also show A to M. Exactly like table B. That shows me the counts I need.

[Glenn Kennedy]

One last shot. Look at any of the cells in Table B, in the attachment to Jete's reply at Post 6. Show me just ONE that gives you the wrong result.

[wabbak]

Ok, I put the name table B to the right of my table, should have been to the left. My mistake. I thought it would be clear that I am comparing my tables, because that is what I want to see.
None of the results in Jete's post show a number. I need numbers. I have only talked about how may times players play with each other: player A has played with B and C twice and F and D once, just as in my table B. Numbers, values is waht I need.

[Glenn Kennedy]

One of us is being dense. It's probably me. It usually is. Close all copies of your file. Open ONLY the attached file. It is Jete's file, with a pile of cells that I formatted yellow. They all contain Jete's formulae (they were ALWAYS there). Give me the cell ref. of any of those cells which are giving the wrong answer in the format: "E18 should read XXx not YYY".

[wabbak]

Ahh, I saw a file that had a new table attached, namely the one to the right, done by Jete. I didn't look in my own table, namely table B, As I was under the impression that it was all about the table to the right. That does make a difference. There was no indication that my table had been altered. Ik will now study the file again. Thanks.