Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Question | Excel Discussion (Misc queries) | |||
Counting rows of blanks across certain columns | New Users to Excel | |||
Counting and Summing | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Counting question | Excel Discussion (Misc queries) |