Exclude a whole sub group based on one record and then repeat.

JeffGrant

JeffGrant

Well-known Member
Joined
Apr 7, 2021
Messages
516
Office Version
  1. 365
Platform
  1. Windows
Hi All, I have a curly one that I don't have any idea who to tackle. Test data below.

Column A is a main Group of race tracks, Column B is a sub group of race numbers, Column C is individual results.
Just in case this infor is required, on any one day, Max Column A is 20 main tracks, Max Column B is 12 races, Max Column C is 24 individual entrants.

What I am trying to do is this.

For each sub group in column B, if the length of the cell in column C of ANY individual is <=3, then in column D, enter the Value "No" for the entire sub group. Then repeat for the next sub group.

Basically, if there is an entrant (column c) in a subgroup (column b), that does not have at least 3 previous results, then the entire subgroup is disqualified. (not fair i know, but that is the beauty of self regulation :))

All ideas and suggestions on how to handle this are very very graciously received on this one.

test.xlsx
ABC
1trackrace numberlast10
2Gosford23x20
3Gosford23x20
4Gosford23x20
5Gosford29x
6Gosford279x5774x
7Gosford279x5774x
8Gosford279x5774x
9Gosford20
10Gosford250365x9
11Gosford250365x9
12Gosford250365x9
13Gosford26322x8
14Gosford26322x8
15Gosford26322x8
16Gosford28x
17Gosford242
18Gosford242
19Gosford34588072052
20Gosford34588072052
21Gosford34588072052
22Gosford34220x47
23Gosford34220x47
24Gosford34220x47
25Gosford365x9
26Gosford365x9
27Gosford365x9
28Gosford3886
29Gosford3886
30Gosford3886
31Gosford37
32Gosford324x852
33Gosford324x852
34Gosford324x852
35Gosford358x820
36Gosford358x820
37Gosford358x820
38Gosford32
39Gosford345
40Gosford345
41Gosford43x1
42Gosford43x1
43Gosford4312
44Gosford4312
45Gosford4312
46Gosford40x9714x
47Gosford40x9714x
48Gosford40x9714x
49Gosford41
50Gosford47x17x073305x7
51Gosford47x17x073305x7
52Gosford47x17x073305x7
53Gosford438x15700066x
54Gosford438x15700066x
55Gosford438x15700066x
56Northam45x724x6x3
57Northam45x724x6x3
58Northam45x724x6x3
59Northam432x
60Northam432x
61Northam420x9
62Northam420x9
63Northam420x9
64Northam40x0x
65Northam40x0x
66Northam4025x
67Northam4025x
68Northam4025x
69Northam40637x
70Northam40637x
71Northam40637x
72Northam40x
73Northam460x4
74Northam460x4
75Northam460x4
76Northam583880x
77Northam583880x
78Northam583880x
79Northam56557x35
80Northam56557x35
81Northam56557x35
82Northam52
83Northam54x
84Northam5347
85Northam5347
86Northam5347
87Northam567x53
88Northam567x53
89Northam567x53
90Northam585
91Northam585
92Northam576x
93Northam576x
94Northam507
95Northam507
96Northam647211612x46
97Northam647211612x46
98Northam647211612x46
99Northam6383x3319846
100Northam6383x3319846
101Northam6383x3319846
102Northam6760x70x76334
103Northam6760x70x76334
104Northam6760x70x76334
105Northam61615x027736
106Northam61615x027736
107Northam61615x027736
108Northam63282146766
109Northam63282146766
110Northam63282146766
111Northam6245585x5250
112Northam6245585x5250
113Northam6245585x5250
114Northam69x123669308
115Northam69x123669308
116Northam69x123669308
117Northam6813606x0098x
118Northam6813606x0098x
119Northam6813606x0098x
120Northam776x32532153
121Northam776x32532153
122Northam776x32532153
123Northam78707402115
124Northam78707402115
125Northam78707402115
126Northam765x331x23
127Northam765x331x23
128Northam765x331x23
129Northam7210x53734x70
130Northam7210x53734x70
131Northam7210x53734x70
132Northam72341x494
133Northam72341x494
134Northam72341x494
135Northam74555150452
136Northam74555150452
137Northam74555150452
138Northam7777x7574189
139Northam7777x7574189
140Northam7777x7574189
141Northam75064108055
142Northam75064108055
143Northam75064108055
144Northam76539887133
145Northam76539887133
146Northam76539887133
147Rockhampton13320x342323
148Rockhampton13320x342323
149Rockhampton13320x342323
150Rockhampton129x5x6292632
151Rockhampton129x5x6292632
152Rockhampton129x5x6292632
153Rockhampton17562234
154Rockhampton17562234
155Rockhampton17562234
156Rockhampton1355343557x3
157Rockhampton1355343557x3
158Rockhampton1355343557x3
159Rockhampton1947x774
160Rockhampton1947x774
161Rockhampton1947x774
162Rockhampton17422x4947
163Rockhampton17422x4947
164Rockhampton17422x4947
165Rockhampton12234376459
166Rockhampton12234376459
167Rockhampton12234376459
168Rockhampton156x587x27069
169Rockhampton156x587x27069
170Rockhampton156x587x27069
171Rockhampton17x075065775
172Rockhampton17x075065775
173Rockhampton17x075065775
174Rockhampton33215183224
175Rockhampton33215183224
176Rockhampton33215183224
177Rockhampton324x69087744
178Rockhampton324x69087744
179Rockhampton324x69087744
180Rockhampton34650672336
181Rockhampton34650672336
182Rockhampton34650672336
183Rockhampton342216x77642
184Rockhampton342216x77642
185Rockhampton342216x77642
186Rockhampton31286539123
187Rockhampton31286539123
188Rockhampton31286539123
189Rockhampton383614990x60
190Rockhampton383614990x60
191Rockhampton383614990x60
192Rockhampton31040x002098
193Rockhampton31040x002098
194Rockhampton31040x002098
195Rockhampton58434131672x
196Rockhampton58434131672x
197Rockhampton58434131672x
198Rockhampton51235x446529
199Rockhampton51235x446529
200Rockhampton51235x446529
201Rockhampton54247135145x
202Rockhampton54247135145x
203Rockhampton54247135145x
204Rockhampton592370x7755x3
205Rockhampton592370x7755x3
206Rockhampton592370x7755x3
207Rockhampton528x80723132
208Rockhampton528x80723132
209Rockhampton528x80723132
210Rockhampton58477277134x
211Rockhampton58477277134x
212Rockhampton58477277134x
213Rockhampton62842x382793
214Rockhampton62842x382793
215Rockhampton62842x382793
216Rockhampton62x246100343
217Rockhampton62x246100343
218Rockhampton62x246100343
219Rockhampton64166x322237x
220Rockhampton64166x322237x
221Rockhampton64166x322237x
222Rockhampton60740x1
223Rockhampton60740x1
224Rockhampton60740x1
225Rockhampton619x77x86476x
226Rockhampton619x77x86476x
227Rockhampton619x77x86476x
228Rockhampton61x32
229Rockhampton61x32
230Rockhampton61x32
231Rockhampton63x952213
232Rockhampton63x952213
233Rockhampton63x952213
234Rockhampton656215534x80x
235Rockhampton656215534x80x
236Rockhampton656215534x80x
237Rockhampton68833136327
238Rockhampton68833136327
239Rockhampton68833136327
240Rockhampton72x232211252
241Rockhampton72x232211252
242Rockhampton72x232211252
243Rockhampton77x464406317
244Rockhampton77x464406317
245Rockhampton77x464406317
246Rockhampton734766x34112x
247Rockhampton734766x34112x
248Rockhampton734766x34112x
249Rockhampton72328x2178x35
250Rockhampton72328x2178x35
251Rockhampton72328x2178x35
252Rockhampton7222121x6553
253Rockhampton7222121x6553
254Rockhampton7222121x6553
255Rockhampton72214x29x35x2
256Rockhampton72214x29x35x2
257Rockhampton72214x29x35x2
258Rockhampton76514462547
259Rockhampton76514462547
260Rockhampton76514462547
261Rockhampton71266x072456
262Rockhampton71266x072456
263Rockhampton71266x072456
264Sapphire Coast17729022231
265Sapphire Coast17729022231
266Sapphire Coast17729022231
267Sapphire Coast14131057x458
268Sapphire Coast14131057x458
269Sapphire Coast14131057x458
270Sapphire Coast145515x89541
271Sapphire Coast145515x89541
272Sapphire Coast145515x89541
273Sapphire Coast13x87x043671x
274Sapphire Coast13x87x043671x
275Sapphire Coast13x87x043671x
276Sapphire Coast116x3
277Sapphire Coast116x3
278Sapphire Coast116x3
279Sapphire Coast110435x53653
280Sapphire Coast110435x53653
281Sapphire Coast110435x53653
282Sapphire Coast158x22407037
283Sapphire Coast158x22407037
284Sapphire Coast158x22407037
285Sapphire Coast278x36x
286Sapphire Coast278x36x
287Sapphire Coast278x36x
288Sapphire Coast28948x
289Sapphire Coast28948x
290Sapphire Coast28948x
291Sapphire Coast28x9
292Sapphire Coast28x9
293Sapphire Coast20x
294Sapphire Coast2703245x90x2
295Sapphire Coast2703245x90x2
296Sapphire Coast2703245x90x2
297Sapphire Coast25x2252x3688x8
298Sapphire Coast25x2252x3688x8
299Sapphire Coast25x2252x3688x8
300Sapphire Coast2354x7323
301Sapphire Coast2354x7323
302Sapphire Coast2354x7323
303Sapphire Coast23
304Sapphire Coast27x3
305Sapphire Coast27x3
306Sapphire Coast3045429x2573
307Sapphire Coast3045429x2573
308Sapphire Coast3045429x2573
309Sapphire Coast30274308114
310Sapphire Coast30274308114
311Sapphire Coast30274308114
312Sapphire Coast37x4211x1881x
313Sapphire Coast37x4211x1881x
314Sapphire Coast37x4211x1881x
315Sapphire Coast3251x216x7186
316Sapphire Coast3251x216x7186
317Sapphire Coast3251x216x7186
318Sapphire Coast34305262221
319Sapphire Coast34305262221
320Sapphire Coast34305262221
321Sapphire Coast342161579x8
322Sapphire Coast342161579x8
323Sapphire Coast342161579x8
324Sapphire Coast4033
325Sapphire Coast4033
326Sapphire Coast4033
327Sapphire Coast49x79230x5
328Sapphire Coast49x79230x5
329Sapphire Coast49x79230x5
330Sapphire Coast43
331Sapphire Coast47046x6722
332Sapphire Coast47046x6722
333Sapphire Coast47046x6722
334Sapphire Coast40087008673
335Sapphire Coast40087008673
336Sapphire Coast40087008673
337Sapphire Coast44x5607
338Sapphire Coast44x5607
339Sapphire Coast44x5607
340Sapphire Coast478209x06865
341Sapphire Coast478209x06865
342Sapphire Coast478209x06865
343Sapphire Coast49x58
344Sapphire Coast49x58
345Sapphire Coast49x58
346Sapphire Coast400x5753x0
347Sapphire Coast400x5753x0
348Sapphire Coast400x5753x0
349Sapphire Coast46
350Sapphire Coast485x075
351Sapphire Coast485x075
352Sapphire Coast485x075
353Sapphire Coast485x00567856
354Sapphire Coast485x00567856
355Sapphire Coast485x00567856
356Sapphire Coast581x5358x8550
357Sapphire Coast581x5358x8550
358Sapphire Coast581x5358x8550
359Sapphire Coast54x322118x040
360Sapphire Coast54x322118x040
361Sapphire Coast54x322118x040
362Sapphire Coast5041344187x5
363Sapphire Coast5041344187x5
364Sapphire Coast5041344187x5
365Sapphire Coast517x07215942
366Sapphire Coast517x07215942
367Sapphire Coast517x07215942
368Sapphire Coast5730371x2505
369Sapphire Coast5730371x2505
370Sapphire Coast5730371x2505
371Sapphire Coast5544x0604526
372Sapphire Coast5544x0604526
373Sapphire Coast5544x0604526
374Sapphire Coast59x093765041
375Sapphire Coast59x093765041
376Sapphire Coast59x093765041
377Sapphire Coast531x5965254x9
378Sapphire Coast531x5965254x9
379Sapphire Coast531x5965254x9
380Sapphire Coast576556165x06
381Sapphire Coast576556165x06
382Sapphire Coast576556165x06
383Sapphire Coast57x869147x995
384Sapphire Coast57x869147x995
385Sapphire Coast57x869147x995
386Sapphire Coast557688x74995
387Sapphire Coast557688x74995
388Sapphire Coast557688x74995
389Sapphire Coast64331859666x
390Sapphire Coast64331859666x
391Sapphire Coast64331859666x
392Sapphire Coast66013130x622
393Sapphire Coast66013130x622
394Sapphire Coast66013130x622
395Sapphire Coast62x452214x706
396Sapphire Coast62x452214x706
397Sapphire Coast62x452214x706
398Sapphire Coast60961142x443
399Sapphire Coast60961142x443
400Sapphire Coast60961142x443
401Sapphire Coast613620x96622
402Sapphire Coast613620x96622
403Sapphire Coast613620x96622
404Sapphire Coast64x745x16955x6
405Sapphire Coast64x745x16955x6
406Sapphire Coast64x745x16955x6
407Sapphire Coast6154189x8642
408Sapphire Coast6154189x8642
409Sapphire Coast6154189x8642
410Sapphire Coast7421486x551x4
411Sapphire Coast7421486x551x4
412Sapphire Coast7421486x551x4
413Sapphire Coast7442324x1512
414Sapphire Coast7442324x1512
415Sapphire Coast7442324x1512
416Sapphire Coast7728x1890
417Sapphire Coast7728x1890
418Sapphire Coast7728x1890
419Sapphire Coast78x12x65448
420Sapphire Coast78x12x65448
421Sapphire Coast78x12x65448
422Sapphire Coast771200x32231
423Sapphire Coast771200x32231
424Sapphire Coast771200x32231
425Sapphire Coast7139x630
426Sapphire Coast7139x630
427Sapphire Coast7139x630
428Sapphire Coast72413140x006
429Sapphire Coast72413140x006
430Sapphire Coast72413140x006
431Sapphire Coast7426x145
432Sapphire Coast7426x145
433Sapphire Coast7426x145
434Sapphire Coast75x7567157x20
435Sapphire Coast75x7567157x20
436Sapphire Coast75x7567157x20
437Sapphire Coast74000x960497x
438Sapphire Coast74000x960497x
439Sapphire Coast74000x960497x
440Warrnambool1452
441Warrnambool1452
442Warrnambool1452
443Warrnambool104x907x977
444Warrnambool104x907x977
445Warrnambool104x907x977
446Warrnambool1639x507
447Warrnambool1639x507
448Warrnambool1639x507
449Warrnambool176x55x734363
450Warrnambool176x55x734363
451Warrnambool176x55x734363
452Warrnambool108694539x06
453Warrnambool108694539x06
454Warrnambool108694539x06
455Warrnambool1525x47244
456Warrnambool1525x47244
457Warrnambool1525x47244
458Warrnambool105x7764
459Warrnambool105x7764
460Warrnambool105x7764
461Warrnambool1682
462Warrnambool1682
463Warrnambool1682
464Warrnambool25806630153
465Warrnambool25806630153
466Warrnambool25806630153
467Warrnambool25x51217
468Warrnambool25x51217
469Warrnambool25x51217
470Warrnambool29177460x240
471Warrnambool29177460x240
472Warrnambool29177460x240
473Warrnambool2177x2917544
474Warrnambool2177x2917544
475Warrnambool2177x2917544
476Warrnambool292105x70702
477Warrnambool292105x70702
478Warrnambool292105x70702
479Warrnambool2251x1x2
480Warrnambool2251x1x2
481Warrnambool2251x1x2
482Warrnambool24005111x0x70
483Warrnambool24005111x0x70
484Warrnambool24005111x0x70
485Warrnambool3669x
486Warrnambool3669x
487Warrnambool3669x
488Warrnambool3237
489Warrnambool3237
490Warrnambool3237
491Warrnambool35
492Warrnambool385
493Warrnambool385
494Warrnambool36x6x
Sheet1
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
JeffGrant said:
if the length of the cell in column C of ANY individual is <=3, then in column D, enter the Value "No"
Click to expand...

JeffGrant said:
that does not have at least 3 previous results,
Click to expand...
Aren't those inconsistent?

Try this in D2, copied down. Depending on the above, may need to change <3 to <4

Excel Formula: 
=IF(COUNT(FILTER(B$2:B$494,(A$2:A$494=A2)*(B$2:B$494=B2)*(LEN(C$2:C$494)<3),"")),"No","")
 
Upvote 0
Solution
Oh My!!!! Peter, I can not even begin to explain how much I am in awe of the skills on the forum.

That is perfect. And here am I having spent a couple of hours working through nested loops in VBA to achieve what you have done in one line.

Thank you ever so much.
 
Upvote 0
You're welcome. Glad it worked for you. :)

BTW, the 'Mark as solution' button is to mark the actual post that has the best answer for you, not just that the question is solved. Therefore, in this case, I have moved the mark from post 3 to post 2.
If better answers come along later you can always moved it. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

JeffGrant
  • Solved
Array output - very very strange - I am a complete loss
Replies
4
Views
335
Peter_SSs
Peter_SSs
N
  • Solved
Counting Multi Criteria Occurrences in a Group of Records - vba
2 3
Replies
22
Views
833
Nicha
N
P
  • Question
Command Buttons on Userform
Replies
1
Views
572
palaeontology
P
S
  • Question
For Loop with If Statement Assistance
Replies
0
Views
231
smick
S
D
  • Question
Issue with EntireRow.Delete
2
Replies
11
Views
780
RoryA
RoryA

Forum statistics

Threads
1,214,827
Messages
6,121,809
Members
449,048
Latest member
greyangel23

Share this page

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