Check my entire betting sets with whole results

Kishan · Nov 13, 2017

Using Excel 2000

Hi,

This is challenging task;

I got results in the columns C:P (R1 Through R14)
I got betting sets in the columns R:AE (B1 Through B14)
I want to check my entire betting sets R:AE with whole results C:P
Check the matches only if the correct are grater than 9 to 14, because less than 10 winning price value are 0
And show matching results grater than 9 to 14 in columns AG:AK

The below example is showing some matching results...
Note: as this is an example so it has show with few data sets, Actually I have 4000+ rows with results, which I want to check with 19000+ rows betting sets

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

4

5

R1

R2

R3

R4

R5

R6

R7

R8

R9

R10

R11

R12

R13

R14

B1

B2

B3

B4

B5

B6

B7

B8

B9

B10

B11

B12

B13

B14

10

11

12

13

14

6

X

1

2

1

2

1

2

X

1

X

1

3

0

1

2

0

7

X

2

1

X

1

2

1

X

2

1

2

1

X

1

2

0

8

1

2

1

2

1

X

1

X

2

X

1

0

9

2

1

X

2

1

X

1

2

1

X

1

X

1

X

1

2

1

0

10

1

X

1

X

1

2

1

X

1

X

2

1

2

1

2

1

0

11

X

2

X

2

X

1

2

1

2

X

1

X

1

X

1

X

1

2

1

0

12

X

1

2

1

2

1

X

1

X

1

2

X

1

2

1

X

1

X

1

0

13

1

X

2

1

X

1

2

1

X

1

2

X

1

2

1

X

1

X

1

0

14

1

2

X

1

X

1

X

1

2

X

1

2

1

X

1

2

0

15

1

X

1

X

1

2

X

1

X

1

2

1

2

1

2

1

0

1

0

16

X

1

X

2

1

2

X

1

X

1

2

X

1

X

1

X

2

0

17

1

X

1

2

1

X

1

2

1

X

1

2

X

2

X

2

X

1

X

2

0

18

1

X

1

2

X

1

19

X

1

X

1

2

1

X

20

1

2

1

X

1

2

X

1

X

2

21

1

2

1

X

1

2

1

X

1

22

2

1

X

1

X

1

2

1

2

23

1

2

1

2

1

X

1

24

X

1

2

1

2

1

X

1

X

25

1

X

1

26

X

1

2

1

X

1

X

1

27

1

2

X

1

2

1

X

1

28

1

2

1

2

X

1

X

1

X

29

1

2

1

X

1

X

1

2

1

X

2

30

1

X

1

X

1

X

1

2

31

1

X

1

32

1

X

1

2

1

X

1

X

2

1

33

2

1

X

1

X

2

1

2

1

X

1

34

1

X

1

2

1

X

1

X

1

35

2

1

2

X

1

X

1

X

1

36

1

X

2

1

2

X

1

X

1

2

X

37

X

1

2

X

38

X

2

1

X

1

2

X

1

39

X

1

2

1

2

1

X

1

2

40

1

X

1

2

X

1

2

1

X

1

41

X

1

X

1

X

1

42

Sheet1

Thank you in advance

Regards,
Kishan

Eric W · Nov 13, 2017

I'm a bit confused.

Do you want to compare C6:P6 with every row in the range R6:AR17? And when you compare them, you're checking to see if C6=R6, D6=S6, E6=T6, etc.? So Row 6 compared with row 6 gives a count of 8, and row 6 compared with row 7 gives a count of 6? Then in AG6:AK6 list the count of rows that match the given count?

If so, try this:

Code:

Sub MatchSets()
Dim r1 As Long, r2 As Long, i As Long, j As Long, Arr1 As Variant, Arr2 As Variant, Arr3() As Long, ctr As Long

    r1 = Cells(Rows.Count, "C").End(xlUp).Row
    r2 = Cells(Rows.Count, "R").End(xlUp).Row
    
    Arr1 = Range("C6:P" & r1).Value
    Arr2 = Range("R6:AE" & r2).Value
    ReDim Arr3(0 To UBound(Arr1), 1 To 5)
    For i = 1 To 5
        Arr3(0, i) = i + 9
    Next i
    
    For i = 1 To UBound(Arr1)
        For j = 1 To UBound(Arr2)
            ctr = 0
            For k = 1 To 14
                If Arr1(i, k) = Arr2(j, k) Then ctr = ctr + 1
            Next k
            If ctr > 9 Then Arr3(i, ctr - 9) = Arr3(i, ctr - 9) + 1
        Next j
    Next i
    
    Cells(5, "AG").Resize(UBound(Arr1), 5).Value = Arr3
    
End Sub

The results I got for the sample data do not match your values at all. Also, if your range is 4000 times 19,000, the number of loops would be something like 4000 X 19000 X 14 = 1.064 billion, so it might take a while. It's possible that a more clever algorithm could speed it up some, but this version is about as optimized as I can do.

Kishan · Nov 13, 2017

Eric W said:
I'm a bit confused.

Do you want to compare C6:P6 with every row in the range R6:AR17? And when you compare them, you're checking to see if C6=R6, D6=S6, E6=T6, etc.? So Row 6 compared with row 6 gives a count of 8, and row 6 compared with row 7 gives a count of 6? Then in AG6:AK6 list the count of rows that match the given count?

If so, try this:

Code:

Sub MatchSets() Dim r1 As Long, r2 As Long, i As Long, j As Long, Arr1 As Variant, Arr2 As Variant, Arr3() As Long, ctr As Long r1 = Cells(Rows.Count, "C").End(xlUp).Row r2 = Cells(Rows.Count, "R").End(xlUp).Row Arr1 = Range("C6:P" & r1).Value Arr2 = Range("R6:AE" & r2).Value ReDim Arr3(0 To UBound(Arr1), 1 To 5) For i = 1 To 5 Arr3(0, i) = i + 9 Next i For i = 1 To UBound(Arr1) For j = 1 To UBound(Arr2) ctr = 0 For k = 1 To 14 If Arr1(i, k) = Arr2(j, k) Then ctr = ctr + 1 Next k If ctr > 9 Then Arr3(i, ctr - 9) = Arr3(i, ctr - 9) + 1 Next j Next i Cells(5, "AG").Resize(UBound(Arr1), 5).Value = Arr3 End Sub

The results I got for the sample data do not match your values at all. Also, if your range is 4000 times 19,000, the number of loops would be something like 4000 X 19000 X 14 = 1.064 billion, so it might take a while. It's possible that a more clever algorithm could speed it up some, but this version is about as optimized as I can do.

Hi Eric, first of all big thanks for making a smart code, which is lifesaver, checking only the few lines manually require hours and hours, I like the macro very much! And also it is giving the same results as shown in my example post#1 only the way is different here is my view point please if you could alter I will be grateful to you.

Eric W said:
I And when you compare them, you're checking to see if C6=R6, D6=S6, E6=T6, etc.?

Yes this is the idea, you got it perfectly right.

Comparing method, I want to compare each betting sets is in range R:AE with entire results is in range C:P, and put the final result in AG:AK

For example check 1st betting row R6:AE6 with whole results, in the example below comparing the betting row R6:AE6 with results data there are 3 row with 10 winners, 1 row with 12 winners, and 2 rows with 13 winners so the final results 3, 1 & 2 is showing in row AG6:AK6 for betting set R6:AE6 and this comparing method will be continue for every betting rows.

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

4

14

2

1

2

0

5

R1

R2

R3

R4

R5

R6

R7

R8

R9

R10

R11

R12

R13

R14

B1

B2

B3

B4

B5

B6

B7

B8

B9

B10

B11

B12

B13

B14

10

11

12

13

14

6

X

1

2

1

2

1

2

X

1

X

1

3

0

1

2

0

7

X

2

1

X

1

2

1

X

2

1

2

1

X

1

2

0

8

1

2

1

12

1

2

1

X

1

X

2

X

1

0

9

2

1

X

2

1

X

1

2

1

X

1

X

1

X

1

2

1

0

10

1

X

1

X

1

2

1

X

1

X

2

1

2

1

2

1

0

11

X

2

X

2

X

1

2

1

2

X

1

X

1

X

1

X

1

2

1

0

12

X

1

2

1

2

1

X

1

X

1

2

X

1

2

1

X

1

X

1

0

13

1

X

2

1

X

1

2

1

X

1

2

X

1

2

1

X

1

X

1

0

14

1

2

X

1

X

1

X

1

2

X

1

2

1

X

1

2

0

15

1

X

1

X

1

2

X

1

X

1

2

1

2

1

2

1

0

1

0

16

X

1

X

2

1

2

X

1

X

1

2

X

1

X

1

X

2

0

17

1

X

1

2

1

X

1

2

1

X

1

2

X

2

X

2

X

1

X

2

0

18

1

X

1

2

X

1

10

19

X

1

X

1

2

1

X

20

1

2

1

X

1

2

X

1

X

2

21

1

2

1

X

1

2

1

X

1

22

2

1

X

1

X

1

2

1

2

23

1

2

1

2

1

X

1

24

X

1

2

1

2

1

X

1

X

25

1

X

1

13

26

X

1

2

1

X

1

X

1

27

1

2

X

1

2

1

X

1

28

1

2

1

2

X

1

X

1

X

29

1

2

1

X

1

X

1

2

1

X

2

30

1

X

1

X

1

X

1

2

10

31

1

X

1

13

32

1

X

1

2

1

X

1

X

2

1

33

2

1

X

1

X

2

1

2

1

X

1

34

1

X

1

2

1

X

1

X

1

10

35

2

1

2

X

1

X

1

X

1

36

1

X

2

1

2

X

1

X

1

2

X

37

X

1

2

X

38

X

2

1

X

1

2

X

1

39

X

1

2

1

2

1

X

1

2

40

1

X

1

2

X

1

2

1

X

1

41

X

1

X

1

X

1

42

Sheet1

I hope I have made a bit clearer

Regards,
Kishan

Eric W · Nov 14, 2017

If I understand correctly, then it's just a matter of changing the order that the arrays are compared:

Rich (BB code):

Sub MatchSets()
Dim r1 As Long, r2 As Long, i As Long, j As Long, Arr1 As Variant, Arr2 As Variant, Arr3() As Long, ctr As Long

    r1 = Cells(Rows.Count, "C").End(xlUp).Row
    r2 = Cells(Rows.Count, "R").End(xlUp).Row
    
    Arr1 = Range("C6:P" & r1).Value
    Arr2 = Range("R6:AE" & r2).Value
    ReDim Arr3(0 To UBound(Arr2), 1 To 5)
    For i = 1 To 5
        Arr3(0, i) = i + 9
    Next i
    
    For i = 1 To UBound(Arr2)
        For j = 1 To UBound(Arr1)
            ctr = 0
            For k = 1 To 14
                If Arr2(i, k) = Arr1(j, k) Then ctr = ctr + 1
            Next k
            If ctr > 9 Then Arr3(i, ctr - 9) = Arr3(i, ctr - 9) + 1
        Next j
    Next i
    
    Cells(5, "AG").Resize(UBound(Arr2), 5).Value = Arr3
    
End Sub

Kishan · Nov 15, 2017

Eric W said:

If I understand correctly, then it's just a matter of changing the order that the arrays are compared:

Rich (BB code):

Sub MatchSets()
Dim r1 As Long, r2 As Long, i As Long, j As Long, Arr1 As Variant, Arr2 As Variant, Arr3() As Long, ctr As Long

    r1 = Cells(Rows.Count, "C").End(xlUp).Row
    r2 = Cells(Rows.Count, "R").End(xlUp).Row
    
    Arr1 = Range("C6:P" & r1).Value
    Arr2 = Range("R6:AE" & r2).Value
    ReDim Arr3(0 To UBound(Arr2), 1 To 5)
    For i = 1 To 5
        Arr3(0, i) = i + 9
    Next i
    
    For i = 1 To UBound(Arr2)
        For j = 1 To UBound(Arr1)
            ctr = 0
            For k = 1 To 14
                If Arr2(i, k) = Arr1(j, k) Then ctr = ctr + 1
            Next k
            If ctr > 9 Then Arr3(i, ctr - 9) = Arr3(i, ctr - 9) + 1
        Next j
    Next i
    
    Cells(5, "AG").Resize(UBound(Arr2), 5).Value = Arr3
    
End Sub

Hi Eric, I thought might you need to rewrite, but just changing the orders it is working as appeal.

I am very grateful to you for giving a great solution

Thank you for your kind help

Have a good time

Regards,
Kishan

Check my entire betting sets with whole results

Kishan

Well-known Member

Excel Facts

Eric W

MrExcel MVP

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

Check my entire betting sets with whole results

Kishan

Well-known Member

Excel Facts

Eric W

MrExcel MVP

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock