+ Reply to Thread
Results 1 to 2 of 2

Counting question

  1. #1
    Brad
    Guest

    Counting question


    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




  2. #2
    Bernie Deitrick
    Guest

    Re: Counting question

    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



    "Brad" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1