Check betting sets

motilulla · Oct 10, 2017

Hello,

I have a question I want to check my betting sets with all the results one by one and if matches greater than 10, 11, 12, 13, 14 filter those rows and copy paste them in another sheet one below another.

Data sheet is holding Results in the Columns C:P, Betting sets in the Columns R:AE, I am using formula in cell AF6 = SUMPRODUCT(--($C$6:$P$6=R6:AE6)) copied down to AF55 once match result are calculated I just filter AF row grater than 9 and copy data in sheet "Match Result"
Check for the next row match results I change the formula AF6 = SUMPRODUCT(--($C$7:$P$7=R6:AE6)) copied down to AF55 and repeat the process filter AF row grater than 9 and copy data in sheet "Match Result"

Is there any way it could be auto mechanize to check all result with betting sets and filter the matches and put them in the sheet "Match Result"

Sheet Data

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

1

2

3

4

N

Year

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

EM1

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

Match

5

N

Year

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

EM1

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

Match

6

1

2001

1

2

1

12

7

2

2001

X

1

2

1

2

1

2

X

1

X

1

X

2

X

1

X

2

1

2

1

2

4

8

3

2001

X

2

1

X

1

2

1

X

2

1

X

2

X

2

X

1

2

1

X

2

X

2

9

4

2001

2

1

X

2

1

X

1

2

1

2

1

2

1

2

1

X

1

10

5

2001

1

X

1

X

1

2

1

X

1

2

X

1

X

2

1

2

1

X

8

11

6

2001

X

2

X

2

X

1

2

1

2

X

1

X

2

1

X

1

2

1

7

12

7

2001

X

1

2

1

2

1

X

1

X

1

X

1

2

1

2

1

X

2

1

7

13

X

1

2

1

2

1

2

X

2

X

1

8

14

1

2

1

X

1

2

X

1

X

10

15

1

2

1

X

1

X

2

X

1

X

2

1

5

16

2

1

2

1

X

2

1

X

1

6

17

1

2

X

2

X

2

1

2

X

1

X

4

18

1

2

X

2

1

9

19

X

1

2

1

X

2

X

1

X

4

20

2

1

2

1

2

1

2

1

7

21

X

1

X

1

2

1

2

X

1

2

7

22

1

2

1

X

1

2

X

1

X

10

23

1

2

X

1

2

1

X

1

X

1

8

24

2

1

2

X

2

X

2

1

2

1

X

6

25

X

2

X

1

2

1

2

1

X

1

X

2

4

26

X

1

X

1

X

1

7

27

1

X

1

2

1

2

X

1

9

28

1

X

1

X

1

2

1

2

X

1

8

29

X

2

X

1

X

2

1

7

30

X

1

X

1

X

2

6

31

X

1

2

X

1

X

1

X

2

1

2

4

32

1

2

X

1

X

1

2

1

X

1

6

33

1

2

1

X

1

X

2

X

1

8

34

1

2

X

1

2

1

X

1

7

35

X

1

2

1

2

X

2

1

X

7

36

1

2

1

X

1

X

2

X

1

8

37

X

2

X

1

X

2

1

7

38

1

X

1

X

2

1

X

1

X

2

X

1

8

39

2

1

X

1

X

1

X

2

X

1

9

40

X

2

1

2

1

2

1

2

1

2

1

6

41

1

2

1

X

1

2

8

42

2

1

X

1

2

1

X

2

1

2

X

1

8

43

1

X

1

X

1

2

1

2

X

9

44

X

1

2

1

2

1

X

2

5

45

2

X

1

2

1

X

2

1

X

1

6

46

1

2

1

X

2

1

X

1

2

6

47

1

X

1

2

1

2

1

2

X

8

48

X

1

X

1

2

1

2

1

X

1

8

49

X

1

X

2

1

X

1

8

50

1

X

1

X

2

1

X

1

7

51

1

2

X

1

X

1

6

52

1

2

1

2

X

2

1

2

1

2

1

8

53

1

2

1

2

1

2

1

X

1

10

54

X

1

2

X

1

X

1

2

1

7

55

1

2

X

1

2

1

X

1

X

1

7

Data

Sheet Match Result

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

1

2

3

4

N

Year

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

EM1

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

Match

5

N

Year

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

EM1

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

Match

6

1

2001

1

2

1

12

7

1

2

1

2

1

2

1

X

1

10

8

1

2

1

X

1

2

X

1

X

10

9

1

2

1

X

1

2

X

1

X

10

1

2

1

2

1

2

1

X

1

10

11

12

2

2001

X

1

2

1

2

1

2

X

1

X

1

X

1

2

1

X

2

X

1

X

10

13

X

2

X

1

2

1

2

1

X

1

X

2

10

14

X

1

2

1

2

X

2

1

X

10

15

X

2

1

2

1

2

1

2

1

2

1

10

16

X

1

2

1

2

1

X

2

10

17

1

2

X

1

X

1

10

18

19

3

2001

X

2

1

X

1

2

1

X

2

1

X

2

X

1

X

2

1

10

20

X

1

2

X

1

X

1

X

2

1

2

10

21

X

2

X

1

X

2

1

10

22

23

4

2001

2

1

X

2

1

X

1

2

1

X

2

X

1

X

2

1

10

24

X

1

2

X

1

X

1

X

2

1

2

10

25

X

2

X

1

X

2

1

10

26

27

5

2001

1

X

1

X

1

2

1

X

1

X

2

X

1

X

2

1

10

28

X

1

2

X

1

X

1

X

2

1

2

10

29

X

2

X

1

X

2

1

10

30

31

6

2001

X

2

X

2

X

1

2

1

2

X

2

X

1

X

2

1

10

32

X

1

2

X

1

X

1

X

2

1

2

10

33

X

2

X

1

X

2

1

10

34

35

7

2001

X

1

2

1

2

1

X

1

X

1

X

2

X

1

X

2

1

10

36

X

1

2

X

1

X

1

X

2

1

2

10

37

X

2

X

1

X

2

1

10

Match Result

Thank you all
Excel 2000
Regards,
Moti

motilulla · Oct 11, 2017

Re: Check betting sets not sure is it possible.

Hello, Rick Rothstein, I have a one question I am trying to check my 50 betting sets with over 250 results, but macro is checking only the 50 results and turns off. I am sure would require some adjustment as the question has changed.

Please could you take a look?

Thank you

Regards
Moti

motilulla · Oct 12, 2017

Re: Check betting sets not sure is it possible.

Hello, Rick Rothstein,

I thought better to show changed data sheet here. In this situation the code stop at the line below and if I check debug it gives error 1004

Code:

 Intersect(Columns("R:AE"), Cells(StartRow, "AF").Resize(UBound(SetData)).SpecialCells(xlConstants).EntireRow).Copy .Cells(NextRow, "R")

Please could you take a look?

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

1

2

3

4

N

Year

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

EM1

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

Match

5

N

Year

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

EM1

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

Match

6

1

2001

1

2

1

7

2

2001

X

1

2

1

2

1

2

X

1

X

1

X

2

X

1

X

2

1

2

1

2

8

3

2001

X

2

1

X

1

2

1

X

2

1

X

2

X

2

X

1

2

1

X

2

X

2

9

4

2001

2

1

X

2

1

X

1

2

1

2

1

2

1

2

1

X

1

10

5

2001

1

X

1

X

1

2

1

X

1

2

X

1

X

2

1

2

1

X

11

6

2001

X

2

X

2

X

1

2

1

2

X

1

X

2

1

X

1

2

1

12

7

2001

X

1

2

1

2

1

X

1

X

1

X

1

2

1

2

1

X

2

1

13

8

2001

1

2

X

1

2

1

X

1

X

1

2

1

2

1

2

X

2

X

1

14

9

2001

1

2

1

2

X

1

X

1

X

1

2

1

X

1

2

X

1

X

15

10

2001

1

2

1

X

1

X

1

2

1

X

2

1

2

1

X

1

X

2

X

1

X

2

1

16

11

2001

1

X

1

X

1

X

1

2

1

2

1

X

2

1

X

1

17

12

2001

1

X

1

2

X

2

X

2

1

2

X

1

X

18

13

2001

1

X

1

2

1

X

1

X

2

1

2

X

2

1

19

14

2001

2

1

X

1

X

2

1

2

1

X

1

X

1

2

1

X

2

X

1

X

20

15

2001

1

X

1

2

1

X

1

X

1

2

1

2

1

2

1

2

1

21

16

2001

2

1

2

X

1

X

1

X

1

X

1

X

1

2

1

2

X

1

2

22

17

2001

1

X

2

1

2

X

1

X

1

2

X

1

2

1

X

1

2

X

1

X

23

18

2001

X

1

2

X

1

2

X

1

2

1

X

1

X

1

24

19

2001

X

2

1

X

1

2

X

1

2

1

2

X

2

X

2

1

2

1

X

25

20

2001

X

1

2

1

2

1

X

1

2

X

2

X

1

2

1

2

1

X

1

X

2

26

21

2001

1

X

1

2

X

1

2

1

X

1

X

1

X

1

X

1

27

22

2001

X

1

X

1

X

1

X

1

2

1

2

X

1

28

23

2001

2

X

2

1

X

1

X

1

X

1

2

1

X

1

X

1

2

1

2

X

1

29

24

2001

X

1

2

X

1

2

1

2

1

X

2

X

1

X

2

1

30

25

2001

X

1

X

1

X

1

2

1

X

1

X

1

X

2

31

26

2001

1

X

1

2

1

X

1

X

1

2

X

1

X

1

X

2

1

2

32

27

2001

1

X

2

1

X

1

2

X

1

X

1

2

1

X

1

33

28

2001

2

X

1

X

1

X

1

2

1

X

1

X

2

X

1

34

29

2001

1

2

1

X

2

1

2

1

2

X

1

2

1

X

1

35

30

2001

X

1

X

1

2

1

2

1

X

2

X

2

1

X

1

2

1

2

X

2

1

X

36

31

2001

X

1

X

1

2

1

2

1

X

1

X

2

X

1

37

32

2001

X

1

X

1

X

2

X

1

X

2

1

X

2

X

1

X

2

1

38

33

2001

X

1

X

1

2

X

1

X

1

X

2

1

X

1

X

2

X

1

39

34

2001

X

1

2

X

1

X

2

1

X

1

X

1

X

2

X

1

40

35

2001

2

1

X

2

1

X

1

X

1

2

X

2

1

2

1

2

1

2

1

2

1

41

36

2001

X

2

1

X

1

2

1

2

1

2

1

2

1

X

1

2

42

37

2001

1

X

2

X

1

2

1

2

X

1

2

X

2

1

X

1

2

1

X

2

1

2

X

1

43

38

2001

X

2

X

1

2

1

X

1

X

1

X

1

2

1

2

X

44

39

2001

1

X

1

X

1

X

1

X

1

2

1

2

1

X

2

45

1

2002

1

X

1

X

1

2

X

1

2

1

X

2

1

X

1

46

2

2002

2

1

X

1

2

1

X

1

X

1

2

1

X

2

1

X

1

2

47

3

2002

X

1

2

X

1

X

1

X

1

2

1

2

1

2

X

48

4

2002

2

1

2

1

2

X

1

2

X

1

X

1

2

1

2

1

X

1

49

5

2002

1

2

1

X

2

1

X

1

X

2

1

X

1

50

6

2002

1

X

2

1

X

1

2

1

X

2

1

X

1

X

1

X

2

1

X

1

51

7

2002

1

X

1

X

1

X

1

X

1

2

X

1

X

1

52

8

2002

X

1

2

1

2

X

2

1

2

1

2

1

53

9

2002

X

2

1

2

1

X

1

X

1

2

X

1

2

1

2

1

2

1

X

1

54

10

2002

1

2

1

X

1

X

1

2

X

1

X

1

2

1

55

11

2002

X

1

X

1

2

1

2

1

X

1

2

X

1

2

1

X

1

X

1

56

12

2002

1

2

X

1

X

1

X

1

X

1

57

13

2002

1

X

1

2

X

1

X

2

1

X

58

14

2002

1

X

1

X

1

X

1

2

X

59

15

2002

1

X

1

X

1

X

2

X

1

2

60

16

2002

1

X

2

1

X

1

2

61

17

2002

1

X

1

X

1

2

1

X

62

18

2002

1

X

1

2

1

2

1

63

19

2002

2

X

1

X

1

2

X

1

2

64

20

2002

1

X

1

2

1

X

1

X

2

65

21

2002

1

X

1

X

1

66

22

2002

1

2

1

X

1

X

1

67

23

2002

2

X

2

X

1

2

1

X

1

X

1

X

1

68

24

2002

2

1

X

1

2

X

1

X

69

25

2002

1

X

1

X

1

2

1

X

2

70

26

2002

X

1

2

1

2

X

1

2

X

1

2

71

27

2002

1

X

2

1

X

2

X

1

72

28

2002

1

X

1

X

2

X

1

X

73

29

2002

1

X

1

2

1

X

1

X

1

2

1

74

30

2002

X

1

X

1

X

1

X

1

X

1

75

31

2002

X

1

X

1

X

2

1

2

X

76

32

2002

2

X

1

2

X

1

2

1

77

33

2002

X

1

X

1

X

1

78

34

2002

1

X

1

2

1

2

1

2

X

2

X

79

35

2002

X

2

X

1

X

1

X

1

X

80

36

2002

1

X

1

2

X

81

37

2002

1

2

1

2

X

1

2

X

2

1

X

82

38

2002

1

X

1

2

X

1

X

1

X

1

83

39

2002

1

X

1

2

1

2

1

X

1

84

40

2002

1

X

1

X

1

85

41

2002

X

1

2

X

1

2

X

1

2

X

86

42

2002

1

X

1

X

1

X

1

87

43

2002

1

X

2

1

2

88

44

2002

1

2

X

1

89

45

2002

1

2

1

X

1

90

46

2002

1

X

1

2

1

91

47

2002

X

1

X

1

2

X

1

92

48

2002

X

2

1

2

1

X

1

X

93

49

2002

1

X

2

1

2

X

1

X

94

50

2002

2

1

X

1

X

1

X

1

95

51

2002

1

X

1

2

1

2

1

X

Data

Thank you

Regards
Moti

motilulla · Oct 12, 2017

Re: Check betting sets not sure is it possible.

motilulla said:
Hello, Rick Rothstein,

I thought better to show changed data sheet here. In this situation the code stop at the line below and if I check debug it gives error 1004

Code:

Intersect(Columns("R:AE"), Cells(StartRow, "AF").Resize(UBound(SetData)).SpecialCells(xlConstants).EntireRow).Copy .Cells(NextRow, "R")

Please could you take a look?

Thank you

Regards
Moti

Hello, Rick Rothstein, Sorry for the inconvenience

I do not know what went wrong with my workbook I create a fresh workbook and Re:copy past macro is in the post#6 and I am surprised it is working correct with all results no problem at all.

Thank you very much for your help

Regards
Moti

Marcelo Branco · Oct 12, 2017

Re: Check betting sets not sure is it possible.

I think Rick's code needs an adjustment to handle cases where no row in R6:AE55 (last data sample) has more than 9 matches.
For example, considering row 13 (8 -2001) there is no row in R6:AE55 with more than 9 matches.

I took a completely different approach and created a new version.

Code:

Sub aTestV1()
    Dim dic As Object, vData1 As Variant, vData2 As Variant, vBlank As Variant
    Dim i As Long, j As Long, k As Long, lIndex As Long, lCounter As Long
    Dim lNextFree As Long, bFound As Boolean, lFirstRow As Long
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    Sheets("Match Result").Cells.ClearContents
    
    lFirstRow = 6 '<--adjust to suit
    With Sheets("Data")
        vData1 = .Range("A" & lFirstRow & ":P" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        vData2 = .Range("R" & lFirstRow & ":AE" & .Cells(.Rows.Count, "R").End(xlUp).Row)
        vBlank = .Range("R1:AE1").Value
        .Range("A4:AE5").Copy Sheets("Match Result").Range("A4")
    End With
    
    lNextFree = lFirstRow
    For i = 1 To UBound(vData1)
        Sheets("Match Result").Range("A" & lNextFree).Resize(, 16) = Application.Index(vData1, i, 0)
        bFound = False
        For j = 1 To UBound(vData2, 1)
            lCounter = 0
            For k = 1 To UBound(vData2, 2)
                If vData1(i, k + 2) = vData2(j, k) Then lCounter = lCounter + 1
            Next k
            If lCounter > 9 Then
                bFound = True
                lIndex = lIndex + 1
                dic(lIndex) = Application.Index(vData2, j, 0)
            End If
        Next j
        lIndex = lIndex + 1
        dic(lIndex) = vBlank
        If Not bFound Then
            lIndex = lIndex + 1
            dic(lIndex) = vBlank
            Sheets("Match Result").Range("Q" & lNextFree) = "Not found"
        End If
        lNextFree = lFirstRow + dic.Count
    Next i
    
    With Sheets("Match Result")
        .Range("R" & lFirstRow).Resize(dic.Count, 14) = Application.Index(dic.items, 0, 0)
        .Cells.HorizontalAlignment = xlCenter
    End With
End Sub

M.

Rick Rothstein · Oct 12, 2017

Re: Check betting sets not sure is it possible.

Marcelo Branco said:
I think Rick's code needs an adjustment to handle cases where no row in R6:AE55 (last data sample) has more than 9 matches.

Hmm, I don't know why, but it didn't occur to me that one of the patterns would not have a match of 10 or more. Thanks for noting that. The fix for my code is simple... I just needed to put an error trap around the code line (see the red highlighted lines below) with the SpecialCells call in it...

Code:

[table="width: 500"]
[tr]
	[td]Sub CheckBettingSets()
  Dim Y As Long, S As Long, C As Long, NextRow As Long
  Dim YearData As Variant, SetData As Variant, Matches As Variant
  Const StartRow = 6
  YearData = Range(Cells(StartRow, "A"), Cells(Rows.Count, "P").End(xlUp))
  SetData = Range(Cells(StartRow, "R"), Cells(Rows.Count, "AE").End(xlUp))
  Cells(StartRow, "AF").Resize(UBound(SetData)).Clear
  With Sheets("Match Result")
    .Cells.Clear
    Range(Cells(StartRow - 2, "A"), Cells(StartRow - 1, "AF")).Copy .Cells(StartRow - 2, "A")
    For Y = 1 To UBound(YearData)
      ReDim Matches(1 To UBound(SetData), 1 To 1)
      For S = 1 To UBound(SetData)
        For C = 1 To UBound(SetData, 2)
          If YearData(Y, C + 2) = SetData(S, C) Then Matches(S, 1) = Matches(S, 1) + 1
        Next
        If Matches(S, 1) < 10 Then Matches(S, 1) = ""
      Next
      Cells(StartRow, "AF").Resize(UBound(Matches)) = Matches
      NextRow = .Cells(.Rows.Count, "R").End(xlUp).Offset(2).Row
      If NextRow = StartRow + 3 Then NextRow = NextRow - 1
      Cells(StartRow + Y - 1, "A").Resize(, UBound(YearData, 2)).Copy .Cells(NextRow, "A")
      .Cells(NextRow, "A").Resize(, UBound(YearData, 2)).HorizontalAlignment = xlCenter
      [B][COLOR="#FF0000"]On Error Resume Next[/COLOR][/B]
      Intersect(Columns("R:AE"), Cells(StartRow, "AF").Resize(UBound(SetData)).SpecialCells(xlConstants).EntireRow).Copy .Cells(NextRow, "R")
      [B][COLOR="#FF0000"]On Error GoTo 0[/COLOR][/B]
      Cells(StartRow, "AF").Resize(UBound(SetData)).Clear
    Next
  End With
End Sub[/td]
[/tr]
[/table]

Marcelo Branco · Oct 12, 2017

Re: Check betting sets not sure is it possible.

I have also a version that list only rows that meet the condition (>9 matches)

Code:

Sub aTestV2()
    'List only rows that meet condition (>9 matches)
    Dim dic As Object, vData1 As Variant, vData2 As Variant, vBlank As Variant
    Dim i As Long, j As Long, k As Long, lIndex As Long, lCounter As Long
    Dim lNextFree As Long, bFound As Boolean, lFirstRow As Long
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    Sheets("Match Result").Cells.ClearContents
    lFirstRow = 6 '<--adjust to suit
    With Sheets("Data")
        vData1 = .Range("A" & lFirstRow & ":P" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        vData2 = .Range("R" & lFirstRow & ":AE" & .Cells(.Rows.Count, "R").End(xlUp).Row)
        vBlank = .Range("R1:AE1").Value
        .Range("A4:AE5").Copy Sheets("Match Result").Range("A4")
    End With
    
    lNextFree = lFirstRow
    
    For i = 1 To UBound(vData1)
        bFound = False
        For j = 1 To UBound(vData2, 1)
            lCounter = 0
            For k = 1 To UBound(vData2, 2)
                If vData1(i, k + 2) = vData2(j, k) Then lCounter = lCounter + 1
            Next k
            If lCounter > 9 Then
                bFound = True
                lIndex = lIndex + 1
                dic(lIndex) = Application.Index(vData2, j, 0)
            End If
        Next j
        
        If bFound Then
            Sheets("Match Result").Range("A" & lNextFree).Resize(, 16) = Application.Index(vData1, i, 0)
            lIndex = lIndex + 1
            dic(lIndex) = vBlank
        End If
        lNextFree = lFirstRow + dic.Count
    Next i
    
    With Sheets("Match Result")
        .Range("R" & lFirstRow).Resize(dic.Count, 14) = Application.Index(dic.items, 0, 0)
        .Cells.HorizontalAlignment = xlCenter
    End With
End Sub

M.

motilulla · Oct 12, 2017

Re: Check betting sets not sure is it possible.

Rick Rothstein said:

Hmm, I don't know why, but it didn't occur to me that one of the patterns would not have a match of 10 or more. Thanks for noting that. The fix for my code is simple... I just needed to put an error trap around the code line (see the red highlighted lines below) with the SpecialCells call in it...

Code:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CheckBettingSets()
  Dim Y As Long, S As Long, C As Long, NextRow As Long
  Dim YearData As Variant, SetData As Variant, Matches As Variant
  Const StartRow = 6
  YearData = Range(Cells(StartRow, "A"), Cells(Rows.Count, "P").End(xlUp))
  SetData = Range(Cells(StartRow, "R"), Cells(Rows.Count, "AE").End(xlUp))
  Cells(StartRow, "AF").Resize(UBound(SetData)).Clear
  With Sheets("Match Result")
    .Cells.Clear
    Range(Cells(StartRow - 2, "A"), Cells(StartRow - 1, "AF")).Copy .Cells(StartRow - 2, "A")
    For Y = 1 To UBound(YearData)
      ReDim Matches(1 To UBound(SetData), 1 To 1)
      For S = 1 To UBound(SetData)
        For C = 1 To UBound(SetData, 2)
          If YearData(Y, C + 2) = SetData(S, C) Then Matches(S, 1) = Matches(S, 1) + 1
        Next
        If Matches(S, 1) < 10 Then Matches(S, 1) = ""
      Next
      Cells(StartRow, "AF").Resize(UBound(Matches)) = Matches
      NextRow = .Cells(.Rows.Count, "R").End(xlUp).Offset(2).Row
      If NextRow = StartRow + 3 Then NextRow = NextRow - 1
      Cells(StartRow + Y - 1, "A").Resize(, UBound(YearData, 2)).Copy .Cells(NextRow, "A")
      .Cells(NextRow, "A").Resize(, UBound(YearData, 2)).HorizontalAlignment = xlCenter
      [B][COLOR=#ff0000]On Error Resume Next[/COLOR][/B]
      Intersect(Columns("R:AE"), Cells(StartRow, "AF").Resize(UBound(SetData)).SpecialCells(xlConstants).EntireRow).Copy .Cells(NextRow, "R")
      [B][COLOR=#ff0000]On Error GoTo 0[/COLOR][/B]
      Cells(StartRow, "AF").Resize(UBound(SetData)).Clear
    Next
  End With
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Rick Rothstein, it produce the error as I said in the post#12, but later it disappear when I checked with different results that I commented in the post#13

Thank you so much for making it correct, so not to produce any error next time in such situation.

Regards
Moti

motilulla · Oct 12, 2017

Re: Check betting sets not sure is it possible.

Marcelo Branco said:

I have also a version that list only rows that meet the condition (>9 matches)

Code:

Sub aTestV2()
    'List only rows that meet condition (>9 matches)
    Dim dic As Object, vData1 As Variant, vData2 As Variant, vBlank As Variant
    Dim i As Long, j As Long, k As Long, lIndex As Long, lCounter As Long
    Dim lNextFree As Long, bFound As Boolean, lFirstRow As Long
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    Sheets("Match Result").Cells.ClearContents
    lFirstRow = 6 '<--adjust to suit
    With Sheets("Data")
        vData1 = .Range("A" & lFirstRow & ":P" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        vData2 = .Range("R" & lFirstRow & ":AE" & .Cells(.Rows.Count, "R").End(xlUp).Row)
        vBlank = .Range("R1:AE1").Value
        .Range("A4:AE5").Copy Sheets("Match Result").Range("A4")
    End With
    
    lNextFree = lFirstRow
    
    For i = 1 To UBound(vData1)
        bFound = False
        For j = 1 To UBound(vData2, 1)
            lCounter = 0
            For k = 1 To UBound(vData2, 2)
                If vData1(i, k + 2) = vData2(j, k) Then lCounter = lCounter + 1
            Next k
            If lCounter > 9 Then
                bFound = True
                lIndex = lIndex + 1
                dic(lIndex) = Application.Index(vData2, j, 0)
            End If
        Next j
        
        If bFound Then
            Sheets("Match Result").Range("A" & lNextFree).Resize(, 16) = Application.Index(vData1, i, 0)
            lIndex = lIndex + 1
            dic(lIndex) = vBlank
        End If
        lNextFree = lFirstRow + dic.Count
    Next i
    
    With Sheets("Match Result")
        .Range("R" & lFirstRow).Resize(dic.Count, 14) = Application.Index(dic.items, 0, 0)
        .Cells.HorizontalAlignment = xlCenter
    End With
End Sub

M.

Thank you Marcelo Branco, for the catching the error. And also thank you for making a new version, which is working perfect!! Now no need to put numbers in ROW A

Regards
Moti

Rick Rothstein · Oct 12, 2017

Re: Check betting sets not sure is it possible.

motilulla said:
Rick Rothstein, it produce the error as I said in the post#12, but later it disappear when I checked with different results that I commented in the post#13

Thank you so much for making it correct, so not to produce any error next time in such situation.

Are you saying my new code is still producing an error when run the first time or are you saying it now runs correctly every time?

motilulla · Oct 12, 2017

Re: Check betting sets not sure is it possible.

Rick Rothstein said:
Are you saying my new code is still producing an error when run the first time or are you saying it now runs correctly every time?

Hello,

Rick Rothstein, My English is not bad but is not good too. I am saying recent code posed in the post#15 is running flawless every time.

Have a nice weekend

Thank you for your help

Regards
Moti

Check betting sets

motilulla

Well-known Member

motilulla

Well-known Member

Excel Facts

motilulla

Well-known Member

motilulla

Well-known Member

Marcelo Branco

MrExcel MVP

Rick Rothstein

MrExcel MVP

Marcelo Branco

MrExcel MVP

motilulla

Well-known Member

motilulla

Well-known Member

Rick Rothstein

MrExcel MVP

motilulla

Well-known Member

Similar threads

Forum statistics

Share this page

Check betting sets

Well-known Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

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