Counting question

[Brad]

There are 44 players on 11 tables - people are moving around - What I would
like to know is who has played with whom - example from round 1 and 2
(detail below)

Player 1 has played with 12, 23, 34, 21, 25, and 44.
Player 12 has played with 1, 23, 34, 3, 27, and 35.

How can I do this - By the way players 1 - 11 can move (for this example I
kept them fixed?

Thanks in advance.

Round 1
1 2 3 4 5 6 7 8 9 10 11
12 13 14 15 16 17 18 19 20 21 22
23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44

Round 2
1 2 3 4 5 6 7 8 9 10 11
21 22 12 13 14 15 16 17 18 19 20
25 26 27 28 29 30 31 32 33 23 24
44 34 35 36 37 38 39 40 41 42 43

Round 3
1 2 3 4 5 6 7 8 9 10 11
19 20 21 22 12 13 14 15 16 17 18
27 28 29 30 31 32 33 23 24 25 26
43 44 34 35 36 37 38 39 40 41 42

Round 4 1 2 3 4 5 6 7 8 9 10 11
17 18 19 20 21 22 12 13 14 15 16
29 30 31 32 33 23 24 25 26 27 28
42 43 44 34 35 36 37 38 39 40 41

Round 5 1 2 3 4 5 6 7 8 9 10 11
15 16 17 18 19 20 21 22 12 13 14
31 32 33 23 24 25 26 27 28 29 30
41 42 43 44 34 35 36 37 38 39 40

Round 6 1 2 3 4 5 6 7 8 9 10 11
13 14 15 16 17 18 19 20 21 22 12
33 23 24 25 26 27 28 29 30 31 32
40 41 42 43 44 34 35 36 37 38 39

Round 7 1 2 3 4 5 6 7 8 9 10 11
22 12 13 14 15 16 17 18 19 20 21
24 25 26 27 28 29 30 31 32 33 23
39 40 41 42 43 44 34 35 36 37 38

Round 8 1 2 3 4 5 6 7 8 9 10 11
20 21 22 12 13 14 15 16 17 18 19
26 27 28 29 30 31 32 33 23 24 25
38 39 40 41 42 43 44 34 35 36 37

Round 9 1 2 3 4 5 6 7 8 9 10 11
18 19 20 21 22 12 13 14 15 16 17
28 29 30 31 32 33 23 24 25 26 27
37 38 39 40 41 42 43 44 34 35 36

Round 10 1 2 3 4 5 6 7 8 9 10 11
16 17 18 19 20 21 22 12 13 14 15
30 31 32 33 23 24 25 26 27 28 29
36 37 38 39 40 41 42 43 44 34 35

Round 11 1 2 3 4 5 6 7 8 9 10 11
14 15 16 17 18 19 20 21 22 12 13
32 33 23 24 25 26 27 28 29 30 31
35 36 37 38 39 40 41 42 43 44 34

 

[Bernie Deitrick]

Brad,

You could use a User-Defined-Function. The code below can be used like:

= PlayedWith($B$2:$L$56,1)

or, if cell M2 has the value 1,

= PlayedWith($B$2:$L$56,M2)

(which can then be copied down to make a table....)

This will report the players that Player 1 plays with (unsorted). Note that I assumed that you have
one blank row between your rounds....

HTH,
Bernie
MS Excel MVP

Function PlayedWith(Draw As Range, Player As Integer) As String
Dim myArea As Range
Dim myCol As Range
Dim myCell1 As Range
Dim myCell2 As Range
Dim myRow As Integer

PlayedWith = ""

For myRow = 1 To Draw.Rows.Count Step 5
Set myArea = Draw.Cells(myRow, 1).Resize(4, 11)
Set myCell1 = myArea.Find(Player, , , xlWhole)
If myCell1 Is Nothing Then
GoTo NotFound
Else
End If
Set myCol = Intersect(myCell1.EntireColumn, myArea)
For Each myCell2 In myCol
If myCell2.Value <> Player Then
If PlayedWith = "" Then
PlayedWith = myCell2.Value
Else
If myCell2.Value <> "" Then
PlayedWith = PlayedWith & ", " & myCell2.Value
End If
End If
End If
Next myCell2
NotFound:
Next myRow

End Function