Excel or database filtering

[octobergirl]

Apologies if this is wrong place.

In excel or any database i am looking to filter data according to price of favourites in races.

I am looking to keep all race data in place together,just creating records of races with similar priced favourites.

Grateful if anyone can help.

[doris_day]

You dont say whether you already have the excel file or database. If you have then its quite simple to sort under any column or create a pivot table. Need more info from you as to what you have in place already.

[octobergirl]

I already have excel file,which can be deported to database if necc.

Have columns for :-

Date
Course
Time
Race Type
Horse
Price

I would like to filter so that i have records for all races where the price of favourites fall within various price parameters,while keeping the prices of all the other horses in each race, and not losing them to the filter.

[octobergirl]

Last paragraph in my last post was not very well written.

I want to filter races by prices of favourites.

But to keep races i want to analyse completely intact,and not to just be left with the favourites prices after filtering.

This is proving to be amazingly difficult in excel2010.

I can't find a way of linking a price to the data for a whole race.

[osknows]

Assuming the field names are in the following columns

A - Date
B - Course
C - Time
D - Race Type
E - Horse
F - Price

add the following new fields and formula to identify unique races

G - Race Key
=A2&B2&C2

H - Min Odds
{=MIN(IF(G:G=G2,F:F))}

NOTE: {} is an Array formula and entered as CTRL+SHIFT+ENTER

Then filter on column H instead of column F. If the range is very large then then the array formula is likely to be very slow. An alternative is to add the following UDF to a MODULE and use the custom function

=MINIF(criteriaRange, criteria, minValue)
=MINIF(G:G,G2,F:F)


MODULE:

Code: Select all

Option Explicit

Public Function MINIF(ByVal rngCrit As Range, _
                      ByVal sCrit As String, _
                      ByVal rngMinRng As Range) As Single
         
Dim i As Long
Dim j As Long
Dim l As Long
Dim sngmin As Single
Dim vcellval As Variant

   i = rngCrit.Column
   j = rngMinRng.Column
   For l = 1 To rngCrit.Rows.Count
      vcellval = rngCrit.Parent.Cells(rngCrit.Row + l - 1, j).Value
      If rngCrit.Parent.Cells(rngCrit.Row + l - 1, i).Value = sCrit And _
         IsNumeric(vcellval) = True Then
         If sngmin = 0 Then sngmin = vcellval
         If vcellval < sngmin Then sngmin = vcellval
      End If
     
      If sngmin <> 0 And IsEmpty(vcellval) = True Then Exit For
   Next l
   
   For l = 1 To rngCrit.Rows.Count
      vcellval = rngCrit.Parent.Cells(rngCrit.Row + l - 1, j).Value
      If rngCrit.Parent.Cells(rngCrit.Row + l - 1, i).Value = sCrit And _
         IsNumeric(vcellval) = True Then
         If rngCrit.Parent.Cells(rngCrit.Row + l - 1, j).Value < sngmin Then
            sngmin = rngCrit.Parent.Cells(rngCrit.Row + l - 1, j).Value
         End If
      End If
     
      If sngmin <> 0 And IsEmpty(vcellval) = True Then Exit For
   Next l
   
   MINIF = sngmin
End Function

[octobergirl]

What can i say OSKNOWS ????

Amazing.

Will get back to you.

regards

[doris_day]