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 |
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" wrote in message ... 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 |
All times are GMT +1. The time now is 08:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com