Counting occurrances

[Brad]

This is the data that I have - what I want to count is how many times each
number 2 through 25 are on the same lines as #1

Example - just using the first five rows 7,16,20,25 would all show a 1 and
the remaining numbers would all show 0

Likewise how many times each number 1, 3-25 are on the same line as #2

Please help

24 2 6 13 15
17 3 22 4 14
16 25 1 7 20
5 23 18 21 10
11 19 8 12 9

1 20 21 2 4
8 17 15 18 25
22 12 10 6 7
9 16 13 14 24
23 5 3 19 11

7 22 21 8 12
13 9 17 15 5
24 6 4 25 23
18 1 20 10 3
14 11 2 16 19

7 8 5 24 6
25 4 9 17 1
15 10 23 3 21
19 18 16 22 13
12 14 11 20 2

6 24 25 9 18
20 15 19 23 22
10 13 12 5 16
3 21 14 11 17
2 7 4 1 8

9 22 10 24 21
16 3 6 19 13
2 18 7 17 23
15 1 12 4 11
20 5 8 14 25

17 19 24 10 12
18 14 6 1 23
25 21 13 11 7
16 15 8 3 4
9 20 2 22 5

18 4 11 10 14
17 6 16 21 15
13 23 8 12 20
1 5 22 25 19
24 3 9 7 2

6 20 17 8 18
22 11 24 1 13
3 25 12 2 10
5 4 23 9 16
7 15 14 19 21

8 10 17 1 3
5 16 11 6 20
12 18 25 15 14
13 2 19 7 4
21 9 22 23 24

4 7 3 17 8
10 14 5 11 15
9 6 20 24 22
25 13 16 2 21
1 23 19 12 18

8 3 7 2 17
16 6 24 13 19
15 4 14 18 11
12 9 20 5 22
21 25 10 23 1

19 7 3 22 20
14 12 15 16 5
11 17 4 10 2
6 18 13 25 24
23 21 1 8 9

21 14 22 13 6
4 5 18 20 12
15 24 17 11 2
19 16 1 9 10
7 8 25 23 3

 

[Brad]

I'm looking to count how many times 1 will race everyone else, and how many
times 2 will race everyone else (by car number)

 

[Bernie Deitrick]

Brad,

You could use a User-Defined-Function. Copy the code below into a codemodule in your workbook.
Then, with your race matrix in cells A1:E83, and the numbers 1 through 25 in I1:AG1, and 1 throough
25 in H2:H26, enter this formula into cell I2:

=Races($A$1:$E$83,I$1,$H2)

and copy to I2:AG26

HTH,
Bernie
MS Excel MVP

Function Races(Sched As Range, Car1 As Range, Car2 As Range) As Variant
Dim myRow As Range

Races = 0

If Car1.Value = Car2.Value Then
Races = ""
Exit Function
End If

For Each myRow In Sched.Rows
If Application.CountIf(myRow, Car1.Value) = 1 And _
Application.CountIf(myRow, Car2.Value) = 1 Then
Races = Races + 1
End If
Next myRow

End Function

 

[Brad]

That worked - thanks

Brad,

You could use a User-Defined-Function. Copy the code below into a codemodule in your workbook.
Then, with your race matrix in cells A1:E83, and the numbers 1 through 25 in I1:AG1, and 1 throough
25 in H2:H26, enter this formula into cell I2:

=Races($A$1:$E$83,I$1,$H2)

and copy to I2:AG26

HTH,
Bernie
MS Excel MVP

Function Races(Sched As Range, Car1 As Range, Car2 As Range) As Variant
Dim myRow As Range

Races = 0

If Car1.Value = Car2.Value Then
Races = ""
Exit Function
End If

For Each myRow In Sched.Rows
If Application.CountIf(myRow, Car1.Value) = 1 And _
Application.CountIf(myRow, Car2.Value) = 1 Then
Races = Races + 1
End If
Next myRow

End Function