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

I wrote:
I just filter AF row grater than 9 and copy data in sheet "Match Result"

But this is the correct procedure:
Sorry about error, I just filter AF column (not the row) grater than 9 and copy the match rows in sheet "Match Result"

I have more than 1000 results to check and filter matches row and than copy them in to sheet "Match Result" which manually is not impossible but it is very difficult.

Please need a macro solution, which can do it for me this automatically.

Thank you
Moti

motilulla · Oct 11, 2017

Re: Check betting sets not sure is it possible.

Bump

motilulla · Oct 11, 2017

Re: Check betting sets not sure is it possible.

Please any suggestions any solution with my query?

Marcelo Branco · Oct 11, 2017

Re: Check betting sets not sure is it possible.

Maybe this macro

Try it in a copy of your workbook

Code:

Sub aTest()
    Dim lastRowA As Long, lastNo As Long, rngToFilter As Range
    Dim i As Long, destRow As Long
    
    With Sheets("Data")
        lastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
        lastNo = .Cells(lastRowA, 1).Value
        Set rngToFilter = .Range("R5:AE" & .Cells(.Rows.Count, "R").End(xlUp).Row)
        .Range("AG5") = ""
    End With
        
    On Error Resume Next
    Sheets("Match Result").Select
    If Err.Number <> 0 Then
        Sheets.Add After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        ActiveSheet.Name = "Match Result"
    End If
    On Error GoTo 0
    Cells.ClearContents
    Range("A4:AE5").Value = Sheets("Data").Range("A4:AE5").Value
    
    For i = 1 To lastNo
        Sheets("Data").Range("AG6").Formula = _
            "=SUMPRODUCT(--(INDEX($C$6:$P$" & lastRowA & "," & i & ",0)=R6:AE6))>9"
        destRow = Cells(Rows.Count, "R").End(xlUp).Row + 1
        If destRow > 6 Then destRow = destRow + 1
        Range("A" & destRow).Resize(, 16).Value = Sheets("Data").Range("A" & 5 + i).Resize(, 16).Value
        Range("A" & destRow).Resize(, 2).Font.Color = vbRed
        rngToFilter.AdvancedFilter _
            Action:=xlFilterCopy, CriteriaRange:=Sheets("Data").Range("AG5:AG6"), _
            CopyToRange:=Range("R" & destRow - 1).Resize(, 14), Unique:=False
        If destRow > 6 Then Range("R" & destRow - 1).Resize(, 14).ClearContents
    Next i
    Cells.HorizontalAlignment = xlCenter
End Sub

Hope this helps

M.

Rick Rothstein · Oct 11, 2017

Re: Check betting sets not sure is it possible.

Here is another macro for you to try...

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
      Intersect(Columns("R:AE"), Cells(StartRow, "AF").Resize(UBound(SetData)).SpecialCells(xlConstants).EntireRow).Copy .Cells(NextRow, "R")
      Cells(StartRow, "AF").Resize(UBound(SetData)).Clear
    Next
  End With
End Sub[/td]
[/tr]
[/table]

motilulla · Oct 11, 2017

Re: Check betting sets not sure is it possible.

Marcelo Branco said:
Maybe this macro

Try it in a copy of your workbook

Hope this helps

M.

Marcelo Branco, Thank you very much!! That's exactly fine! This is what I was looking for to get all to be done at once.

After running the code I found errors in my data are shown in the Post1
Result nº4 I found only 3 matches. Your code found 6 matches, which are correct!
Result nº5 I found only 3 matches. Your code found 4 matches, which are correct!
Result nº7 I found only 3 matches. Your code found 4 matches, which are correct!

Appreciate a lot!! Your solution has saved a lot of time to me.

Regards
Moti

Rick Rothstein · Oct 11, 2017

Re: Check betting sets not sure is it possible.

motilulla said:
Marcelo Branco, Thank you very much!! That's exactly fine! This is what I was looking for to get all to be done at once.

After running the code I found errors in my data are shown in the Post1
Result nº4 I found only 3 matches. Your code found 6 matches, which are correct!
Result nº5 I found only 3 matches. Your code found 4 matches, which are correct!
Result nº7 I found only 3 matches. Your code found 4 matches, which are correct!

Appreciate a lot!! Your solution has saved a lot of time to me.

Just wondering if you tried the code that I post or not? It differs from Marcelo's code in that it keeps the formatting from the original data.

Marcelo Branco · Oct 11, 2017

Re: Check betting sets not sure is it possible.

motilulla said:
Marcelo Branco, Thank you very much!! That's exactly fine! This is what I was looking for to get all to be done at once.

You are very welcome.

After running the code I found errors in my data are shown in the Post1
Result nº4 I found only 3 matches. Your code found 6 matches, which are correct!
Result nº5 I found only 3 matches. Your code found 4 matches, which are correct!
Result nº7 I found only 3 matches. Your code found 4 matches, which are correct!

Yeah, I had noticed that, too. I'm glad my code generated the correct results!

M.

motilulla · Oct 11, 2017

Re: Check betting sets not sure is it possible.

Rick Rothstein said:
Here is another macro for you to try...

Rick Rothstein, while replying to Marcelo Branco post I say your code and just fineshed to try it.

Also I admit my opening post result data are not correct, while your and Marcelo Branco macro are giving the perfect results.

I do appreciate your help and for giving an also nice solution.

Rick Rothstein said:
Just wondering if you tried the code that I post or not? It differs from Marcelo's code in that it keeps the formatting from the original data.

Your code has some advantages, which I like.
1st-it it keeps the formatting from the original data. Which is helpful
2nd-it is not necessary to keep numbers in the column "A" 1 through increasing numbers like 1 to 1000. It works with broken series like I can set 1 to 25 for year 2001, 1 to 55 for year 2005 and so etc....it is fine.

Thank you very much for giving a time saying solution too.

Regards
Moti

Check betting sets

motilulla

Well-known Member

Excel Facts

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

Marcelo Branco

MrExcel MVP

Rick Rothstein

MrExcel MVP

motilulla

Well-known Member

Rick Rothstein

MrExcel MVP

Marcelo Branco

MrExcel MVP

motilulla

Well-known Member

Similar threads

Forum statistics

Share this page

Check betting sets

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

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