Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Filter and discard query

  Asked By: Norma    Date: Sep 05    Category: MS Office    Views: 563
  

In my capacity of Athletics Club Statistician I have been asked to produce a Top
10 ranking table per event. Having produced a file of raw data incorporating
AthleteID (unique number), EventID (unique number as a substitute for the actual
event, e.g. 100 Metres, 200 Metres, etc) and the associated result for each
athlete per event. To this end I could have one athlete with numerous results
for the same event, as well as numerous athletes for the same event.

Although I can do much of the work manually by using the 'Filter' option (Filter
by EventID, AthleteID), it is very manual and time consuming. Finally, once I
have eliminated surplus results per athlete (where 12.2 Secs is better than 12.3
Secs, so 12.3 must be discarded) I need to reassemble the list by EventID, then
Result, and then identify the top 10 results in each Event - deleting those
outside the top 10.

My problem is deciphering the sequence of events, and then coding in VBA (very,
very limited knowledge). Although I can, to some degree, use the 'Record a
Macro' feature to do some of the processing, the final data removal is beyond
me, automatically.

The raw data look something like this:

AthleteID?? EventID?? Result?? Action
1?? ?? ?? ?? ?? 1?? ?? ?? ?? 12.3?? ?? Remove
1?? ?? ?? ?? ?? 1???? ?? ??? 12.2??
1?? ?? ?? ?? ?? 1?? ?? ?? ?? 12.7?? ?? Remove
1?? ?? ?? ?? ?? 2?? ?? ?? ?? 25.6?? ?? Remove
1?? ?? ?? ?? ?? 2?? ?? ?? ?? 24.9?? ?? Remove
1?? ?? ?? ?? ?? 2?? ?? ?? ?? 24.8
3?? ?? ?? ?? ?? 3?? ?? ?? ?? 48.8?? ?? Remove
3?? ?? ?? ?? ?? 3?? ?? ?? ?? 49.8?? ?? Remove
3?? ?? ?? ?? ?? 3?? ?? ?? ?? 48.6
2?? ?? ?? ?? ?? 1?? ?? ?? ?? 12.3??
2?? ?? ?? ?? ?? 1?? ?? ?? ?? 12.4?? ?? Remove
2?? ?? ?? ?? ?? 2?? ?? ?? ?? 24.7
2?? ?? ?? ?? ?? 2?? ?? ?? ?? 24.9?? ?? Remove
2?? ?? ?? ?? ?? 3?? ?? ?? ?? 48.4?? ?? Remove
2?? ?? ?? ?? ?? 3?? ?? ?? ?? 46.6


Once all surplus data is deleted the file is then sorted by EventID, then
Result. The top 10 Results are then identified by Event, with the remaining
discarded.

I have looked at the possibility of using the MIN function in collaboration with
a series of FOR/NEXT loops to sequence through each series on
AthleteID/EventID/Result.

Can anybody give some pointers where to start please?

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on Filter and discard query Or get search suggestion and latest updates.




Tagged: