Automatically filter race and distance range

sobrien

Board Regular
Joined
Feb 28, 2006
Messages
179
I have a list that has racing form listing every horses race start in the chronological order they are running that day. For example starts at race 1 number 1 and list all it races and gives a rating for each one. I want to create an automatic filter that takes all the horses for race 1 and every race start they have and puts them in a seperate sheet then isolates every start they have had within 100m of the distance of the race. You can put the distance of the race whereever you want, let's say A1 for example. The data appears as follows (just included race 1 and 2 to keep it small). Also am i cutting and pasting correctly so you can read this? It looks all over the place below. For race 1 assume the distance of the race is a1 and = 2000. i want all race starts for all runners in race 1 from 1900 to 2100 (i.e. 100m either side of the distance)

No nracenum chorsename dmeetdate ctrackcode ndistance nbarrier notrating
1 1 Julich 05-Mar-10 Sale 1010 4 17.5
2 1 Tricky Tricky 30-Jan-10 Flem 1100 5 35.5
3 2 Pacino 20-Feb-10 Caulf. 1800 5 47.5
4 2 Pacino 30-Jan-10 Flem 1600 12 48
5 2 Pacino 16-Jan-10 Flem 1410 8 35
6 2 Pacino 17-Oct-09 Caulf. 2000 6 53
7 2 Pacino 03-Oct-09 R'wick 2000 10 41
8 2 Pacino 19-Sep-09 R'hill 1900 7 54
9 2 Pacino 05-Sep-09 Flem 1600 2 55
10 2 Pacino 15-Aug-09 Caulf. 2000 1 50.5
11 2 Pacino 08-Aug-09 R'wick 2000 5 50
12 2 Pacino 01-Aug-09 Caulf. 2000 8 47
13 2 Pacino 18-Jul-09 Caulf. 1800 7 46
14 2 Pacino 04-Jul-09 Flem 1800 6 49.5
15 2 Pacino 27-Jun-09 Flem 2000 3 46.5
16 2 Pacino 06-Jun-09 Flem 1700 5 44
17 2 Pacino 23-May-09 Caulf. 1600 2 42.5
18 2 Pacino 13-May-09 LAKESI 1200 4 41.5
19 2 Pacino 10-Jan-09 Caulf. 1600 6 38.5
20 2 Pacino 04-Jan-09 Bairns 1600 1 48
21 2 Pacino 30-Nov-08 Wodong 1590 13 45.5
22 2 Pacino 08-Nov-08 Flem 2000 7 42.5
23 2 Pacino 04-Nov-08 Flem 1800 13 36
24 2 Pacino 18-Oct-08 Caulf. 2000 12 49.5
25 2 Pacino 04-Oct-08 Flem 1720 5 46
26 2 Pacino 13-Sep-08 M.V. 1509 9 36
27 2 Pacino 23-Aug-08 W.Farm 1400 7 32
28 2 Pacino 02-Aug-08 Caulf. 1100 6 30.5
29 2 Pacino 10-Nov-07 Flem 2000 8 47.5
30 2 Pacino 03-Nov-07 Flem 2500 3 45.5
31 2 Pacino 20-Oct-07 Caulf. 2000 13 50
32 2 Pacino 14-Oct-07 Cran. 2025 9 47.5
33 2 Pacino 28-Sep-07 M.V. 2040 7 50
34 2 Pacino 12-Sep-07 W'bee 1625 2 44.5
35 2 Pacino 01-Sep-07 Caulf. 1400 10 40
36 2 Pacino 31-Mar-07 R'hill 2000 2 51
37 2 Pacino 17-Mar-07 Colac 2000 7 43
38 2 Pacino 03-Mar-07 Caulf. 1800 9 50
39 2 Pacino 21-Feb-07 Morn. 1500 10 38
40 2 Pacino 10-Feb-07 Caulf. 1400 5 49
41 2 Pacino 27-Jan-07 HILLSI 1400 1 45
42 2 Pacino 23-Sep-06 R'hill 1800 1 42.5
43 2 Pacino 09-Sep-06 Flem 1400 2 38.5
44 2 Pacino 23-Aug-06 W'bee 1400 1 36
45 2 Precedence 26-Feb-10 M.V. 1600 1 49.5
46 2 Precedence 06-Feb-10 Caulf. 1400 1 44
47 2 Precedence 10-Oct-09 Caulf. 2400 11 44
48 2 Precedence 27-Sep-09 Caulf. 1800 11 49.5
49 2 Precedence 12-Sep-09 M.V. 1600 2 51
50 2 Precedence 29-Aug-09 Caulf. 1400 14 42.5
51 2 Precedence 25-Apr-09 Flem 2800 9 53
52 2 Precedence 11-Apr-09 Caulf. 2390 5 41.5
53 2 Precedence 28-Mar-09 Caulf. 2000 10 39.5
54 2 Precedence 05-Mar-09 C'bury 1900 2 42.5
55 2 Precedence 07-Feb-09 R'hill 1200 7 39
56 2 Precedence 11-Oct-08 R'hill 1400 4 48
57 2 Precedence 24-Sep-08 C'bury 1250 1 44.5
58 2 Toulouse Lautrec 26-Feb-10 M.V. 1600 6 30.5
59 2 Toulouse Lautrec 18-Feb-09 Morn. 2400 5 46
60 2 Toulouse Lautrec 01-Feb-09 K'ton 2000 8 47
61 2 Toulouse Lautrec 17-Jan-09 Flem 1610 5 47
62 2 Toulouse Lautrec 31-May-08 Flem 3400 5 45
63 2 Toulouse Lautrec 07-May-08 M.V. 3224 6 45
64 2 Toulouse Lautrec 12-Apr-08 M.V. 3000 1 46.5
65 2 Toulouse Lautrec 02-Apr-08 LAKESI 3000 6 47
66 2 Toulouse Lautrec 24-Mar-08 Caulf. 2406 1 49
67 2 Toulouse Lautrec 01-Mar-08 Caulf. 2000 3 42.5
68 2 Toulouse Lautrec 17-Feb-08 K'more 1600 2 35
69 2 Toulouse Lautrec 24-Oct-07 G'long 2400 1 39
70 2 Toulouse Lautrec 28-Sep-07 M.V. 3000 3 46
71 2 Toulouse Lautrec 16-Sep-07 P'ham 2800 5 43.5
72 2 Toulouse Lautrec 03-Sep-07 Bend 3000 10 41.5
73 2 Toulouse Lautrec 18-Aug-07 Caulf. 2000 9 47.5
74 2 Toulouse Lautrec 04-Aug-07 Caulf. 2000 1 48
75 2 Toulouse Lautrec 21-Jul-07 Caulf. 1800 11 41.5
76 2 Toulouse Lautrec 09-Dec-06 R'hill 2000 2 45
77 2 Toulouse Lautrec 25-Nov-06 E.Farm 3200 8 43.5
78 2 Toulouse Lautrec 07-Nov-06 R'wick 2400 5 50.5
79 2 Toulouse Lautrec 07-Oct-06 R'wick 2000 10 51
80 2 Toulouse Lautrec 30-Sep-06 R'wick 1600 3 48.5
81 2 Toulouse Lautrec 16-Sep-06 R'hill 1300 10 41
82 2 Toulouse Lautrec 01-Apr-06 Caulf. 2400 7 53.5
83 2 Toulouse Lautrec 18-Mar-06 Flem 2500 10 55
84 2 Toulouse Lautrec 04-Mar-06 Caulf. 2000 4 55.5
85 2 Toulouse Lautrec 11-Feb-06 Caulf. 1800 7 57
86 2 Toulouse Lautrec 28-Jan-06 Flem 1410 8 47
87 2 Toulouse Lautrec 14-Jan-06 LAKESI 1200 3 55
88 2 Toulouse Lautrec 14-May-05 G Cst 2400 7 54
89 2 Toulouse Lautrec 30-Apr-05 D'Ben 2020 12 53
90 2 Toulouse Lautrec 02-Apr-05 R'wick 2000 3 59.5
91 2 Toulouse Lautrec 28-Mar-05 R'wick 1600 4 56
92 2 Toulouse Lautrec 12-Mar-05 R'hill 1500 9 53
93 2 Toulouse Lautrec 19-Feb-05 W.Farm 1200 17 58
94 2 Toulouse Lautrec 12-Jun-04 E.Farm 2400 5 60
95 2 Toulouse Lautrec 29-May-04 D'Ben 2020 11 57
96 2 Toulouse Lautrec 10-Apr-04 R'wick 1600 7 55.5
97 2 Toulouse Lautrec 10-Mar-04 R'wick 1400 5 50
98 2 Toulouse Lautrec 25-Jun-03 C'bury 1100 11 40
99 2 Carbon Trader 19-Feb-10 M.V. 2040 5 50.5
100 2 Carbon Trader 06-Feb-10 Caulf. 2000 8 48.5
101 2 Carbon Trader 30-Jan-10 Flem 1600 1 48.5
102 2 Carbon Trader 16-Jan-10 Flem 1610 8 49.5
103 2 Carbon Trader 19-Dec-09 Flem 2000 9 45
104 2 Carbon Trader 11-Dec-09 M.V. 2040 10 43.5
105 2 Carbon Trader 25-Nov-09 LAKESI 2100 1 43.5
106 2 Carbon Trader 15-Nov-09 G'long 1733 1 42.5
107 2 Carbon Trader 28-Oct-09 Bend 1600 13 40.5
108 2 Carbon Trader 17-Oct-09 Bend 1500 10 40
109 2 Carbon Trader 08-Oct-09 Bend 1300 6 39.5
110 2 Carbon Trader 20-Sep-09 Ball't 1200 7 37
111 2 Carbon Trader 09-Oct-08 Bend 1300 5 38.5
112 2 Carbon Trader 07-Jun-08 Flem 2000 3 43
113 2 Carbon Trader 17-May-08 Flem 1600 16 47.5
114 2 Carbon Trader 08-May-08 Bend 1400 5 44
115 2 Carbon Trader 26-Apr-08 HILLSI 1600 8 46.5
116 2 Carbon Trader 09-Apr-08 Cran. 1600 1 45
117 2 Carbon Trader 29-Mar-08 Bend 1400 10 44
118 2 Carbon Trader 16-Mar-08 Cran. 1400 2 44
119 2 Carbon Trader 29-Feb-08 M.V. 1200 11 42.5
120 2 Carbon Trader 11-Oct-07 Bend 1400 7 43.5
121 2 Carbon Trader 30-Sep-07 HILLSI 1400 9 49
122 2 Carbon Trader 20-Sep-07 Cran. 1200 9 47.5
123 2 Carbon Trader 09-Sep-07 Bend 1100 9 50
124 2 Tangalooma 24-Feb-10 HILLSI 1800 6 17.5
125 2 Tangalooma 13-Jun-09 R'hill 2400 1 47
126 2 Tangalooma 16-May-09 Flem 3200 8 50
127 2 Tangalooma 25-Apr-09 R'wick 3200 1 47
128 2 Tangalooma 11-Apr-09 R'wick 2600 14 51.5
129 2 Tangalooma 28-Mar-09 R'hill 2400 6 45.5
130 2 Tangalooma 14-Mar-09 R'wick 2000 7 50.5
131 2 Tangalooma 28-Feb-09 R'hill 1800 6 47
132 2 Tangalooma 14-Feb-09 R'wick 1600 5 46
133 2 Tangalooma 04-Oct-08 R'wick 2400 10 38
134 2 Tangalooma 27-Sep-08 R'wick 2400 7 54
135 2 Tangalooma 18-Sep-08 N'ctle 2300 8 51.5
136 2 Tangalooma 23-Aug-08 W.Farm 2400 2 48
137 2 Tangalooma 13-Aug-08 C'bury 1900 4 43.5
138 2 Tangalooma 26-Jul-08 N'ctle 1850 3 39.5
139 2 Tangalooma 12-Jul-08 N'ctle 1400 6 36.5
140 2 Tangalooma 19-Jan-08 R'hill 2000 5 43
141 2 Tangalooma 31-Dec-07 G'ford 1600 2 38.5
142 2 Tangalooma 15-Dec-07 N'ctle 1400 7 30.5
143 2 Tangalooma 11-Aug-07 N'ctle 1850 12 39.5
144 2 Tangalooma 04-Aug-07 Kem.Gr 1600 6 38
145 2 Tangalooma 19-Jul-07 Wyong 1350 9 39.5
146 2 Aussie Bomber 16-Feb-10 Benlla 1406 9 41
147 2 Aussie Bomber 08-Feb-10 Wang 1600 4 44
148 2 Aussie Bomber 01-Jan-10 D'Quin 1600 4 45
149 2 Aussie Bomber 24-Dec-09 Wagga 1600 1 39
150 2 Aussie Bomber 12-Dec-09 Moulam 1600 6 44
151 2 Aussie Bomber 21-Nov-09 Hay 1600 3 45
152 2 Aussie Bomber 31-Oct-09 Berrig 1500 4 43.5
153 2 Aussie Bomber 13-Oct-09 Echuca 1600 1 43
154 2 Aussie Bomber 03-Oct-09 Wagga 1400 3 37.5
155 2 Aussie Bomber 12-Sep-09 Jeril. 1400 7 40.5
156 2 Aussie Bomber 19-Apr-09 Wagga 1800 10 44
157 2 Aussie Bomber 27-Mar-09 Albury 2000 8 49.5
158 2 Aussie Bomber 15-Mar-09 Albury 2000 3 38
159 2 Aussie Bomber 22-Feb-09 Bend 2400 6 38.5
160 2 Aussie Bomber 05-Feb-09 Seymor 2200 9 41
161 2 Aussie Bomber 22-Jan-09 Seymor 2000 8 40.5
162 2 Aussie Bomber 14-Jan-09 HILLSI 1800 6 44
163 2 Aussie Bomber 01-Jan-09 D'Quin 1600 5 42
164 2 Aussie Bomber 20-Dec-08 Benlla 1612 1 39
165 2 Aussie Bomber 30-Nov-08 Wodong 1400 4 40
166 2 Aussie Bomber 15-Nov-08 Gundag 1180 6 40
167 2 Aussie Bomber 26-May-08 Benlla 1606 9 37
168 2 Aussie Bomber 10-May-08 Albury 2000 2 38.5
169 2 Aussie Bomber 01-May-08 Wagga 2000 4 38.5
170 2 Aussie Bomber 25-Apr-08 D'Quin 2100 2 38.5
171 2 Aussie Bomber 11-Apr-08 Albury 1600 7 37.5
172 2 Aussie Bomber 30-Mar-08 Albury 1400 5 37.5
173 2 Aussie Bomber 09-Mar-08 Corowa 1200 8 33
174 2 Aussie Bomber 28-Dec-07 Wagga 1000 4 28
175 2 Aussie Bomber 16-May-07 HILLSI 1800 2 45.5
176 2 Aussie Bomber 04-May-07 Wagga 1600 1 43.5
177 2 Aussie Bomber 22-Apr-07 Wagga 1400 5 38
178 2 Aussie Bomber 07-Apr-07 Wagga 1200 2 33.5
179 2 Aussie Bomber 23-Mar-07 Albury 1175 1 32
180 2 Aussie Bomber 11-Mar-07 Corowa 1000 9 20
181 2 Aussie Bomber 23-Feb-07 Albury 1000 11 30
182 2 Aussie Bomber 05-May-06 Wagga 1000 13 27.5
183 2 Fordee 24-Feb-10 HILLSI 1800 13 44
184 2 Fordee 03-Feb-10 HILLSI 1800 6 41.5
185 2 Fordee 03-Nov-09 Flem 2800 10 36.5
186 2 Fordee 28-Oct-09 Bend 2200 13 48.5
187 2 Fordee 18-Oct-09 Seymor 2200 6 42.5
188 2 Fordee 03-Oct-09 Flem 2520 11 47.5
189 2 Fordee 05-Sep-09 Flem 2000 8 46.5
190 2 Fordee 08-Aug-09 Flem 1410 5 47.5
191 2 Fordee 09-Mar-09 Mphvl 3200 8 50.5
192 2 Fordee 28-Feb-09 Mphvl 2600 1 49.5
193 2 Fordee 18-Feb-09 Morn. 2400 1 46.5
194 2 Fordee 09-Feb-09 Hobart 2200 5 46
195 2 Fordee 26-Jan-09 Caulf. 2000 12 45.5
196 2 Fordee 03-Jan-09 Caulf. 2000 12 45
197 2 Fordee 17-Dec-08 HILLSI 1800 2 46
198 2 Fordee 27-Nov-08 Bend 1600 10 43
199 2 Fordee 07-Jun-08 E.Farm 2400 9 52
200 2 Fordee 04-May-08 LAKESI 2100 9 51
201 2 Fordee 16-Apr-08 HILLSI 1800 13 47
202 2 Fordee 08-Nov-07 Flem 1800 3 44.5
203 2 Fordee 25-Oct-07 Cran. 2025 6 45.5
204 2 Fordee 12-Oct-07 Wang 2010 4 39.5
205 2 Fordee 29-Sep-07 Seymor 1617 11 41.5
206 2 Viking Hero 27-Feb-10 Caulf. 1800 5 48.5
207 2 Viking Hero 13-Feb-10 M.V. 1600 6 49
208 2 Viking Hero 23-Jan-10 LAKESI 1300 4 47
209 2 Viking Hero 05-Nov-09 Flem 1800 3 48
210 2 Viking Hero 14-Oct-09 Caulf. 1600 5 43
211 2 Viking Hero 04-Oct-09 Benlla 1506 3 37.5
212 2 Viking Hero 06-Sep-09 Wodong 1300 4 25
213 3 Marveen 06-Mar-10 Flem 1400 5 50.5
214 3 Marveen 20-Feb-10 Caulf. 1100 7 45
215 3 Marveen 09-Jan-10 Caulf. 1200 4 44.5
216 3 Marveen 20-Jun-09 E.Farm 1400 13 46.5
217 3 Marveen 16-May-09 D'Ben 1615 2 52
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
HI
I suggest you look into the custom option in autofilter. goto DATA >Filter >Autofilter > choose custom in dropdown > choose options in multiple columns > OK (you can record a macro)
Assuming dates are in col D and distance in col F
try the following macro. it lists all races of today with distance quoted in A1(+/-10%)
Code:
Sub Macro2()
    Range("A2:Z" & Range("A" & Rows.Count).End(xlUp).Row).Select
           Selection.AutoFilter Field:=4, Criteria1:=Date, Operator:=xlAnd
    Selection.AutoFilter Field:=6, Criteria1:=Cells(1, 1) * 0.9, Operator:=xlAnd, _
        Criteria2:=Cells(1, 1) * 1.1
        Selection.SpecialCells(xlCellTypeVisible).Copy
        Workbooks.Add
    Sheets("Sheet1").Range("A2").PasteSpecial
    ActiveWorkbook.SaveAs Filename:="C:\" & "summary.xls"
msgbox "Complete"
End Sub
ravi
 
Upvote 0
Hi

This macro will copy the selected race for all entries +/- 100 of the race distance which you suggested should be in cell A1.

Code:
Sub RacedataSummary()
'
' RacedataSummary Macro
' Macro recorded 19/03/2010 by Mike Ballard
'
Dim SelRaceNo
 
SelRaceNo = InputBox("Enter required RaceNo ")
Sheets("RaceSummary").Range("A1:G" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents
 
    Range("B1:H" & Range("B" & Rows.Count).End(xlUp).Row).Select
 
    With Selection
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=SelRaceNo
        .AutoFilter Field:=5, Criteria1:="<=" & Range("A1") + 100, Operator:=xlAnd, Criteria2:=">=" & Range("A1") - 100
        .Copy Destination:=Sheets("RaceSummary").Range("A1")
        .AutoFilter
    End With
 
'
End Sub

You will need to create a sheet "RaceSummary" in your workbook.

The module handles the clearance of previous race summary and resets the Autofilter.

hth

Mike
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top