Filtering automatically to select the first cell

[sobrien]

I download form for horse racing and am trying to create a summary sheet which selects the data from the first row only for each horse. The problem is each horse has had a different amount of starts

I want it to appear on a separate sheet as follows :

Race chorsename dmeetdate cgoing ndistance Rating
1 Hillfa 11-Feb-06 G 1200 45.5
1 Isanami 11-Feb-06 G 1200 36.5
1 Leveller 04-Feb-06 G 1400 45
1 Benko 11-Feb-06 G 1200 44.5

The form I download comes in this format :
Race chorsename dmeetdate cgoing ndistance Rating
1 Hillfa 11-Feb-06 G 1200 45.5
1 Hillfa 12-Nov-05 D 1600 39.5
1 Hillfa 01-Nov-05 D 1400 48.5
1 Hillfa 12-Oct-05 G 1600 33.5
1 Hillfa 01-Oct-05 G 1610 40
1 Hillfa 17-Sep-05 G 1600 45.5
1 Hillfa 10-Sep-05 D 1500 43.5
1 Hillfa 27-Aug-05 S 1400 46
1 Hillfa 17-Aug-05 D 1200 40.5
1 Hillfa 05-Mar-05 D 1200 43.5
1 Hillfa 23-Feb-05 G 1200 47
1 Hillfa 11-Feb-05 G 1200 46
1 Hillfa 30-Jan-05 G 1000 41.5
1 Hillfa 09-Dec-04 G 1000 40
1 Isanami 11-Feb-06 G 1200 36.5
1 Isanami 03-Nov-05 G 2500 48.5
1 Isanami 29-Oct-05 G 2000 48.5
1 Isanami 12-Oct-05 G 1600 47.5
1 Isanami 01-Oct-05 G 1610 46
1 Isanami 17-Sep-05 H 1400 51
1 Isanami 10-Sep-05 D 1200 48
1 Isanami 27-Aug-05 S 1100 44
1 Isanami 14-May-05 G 1600 49
1 Isanami 07-May-05 G 1620 40
1 Isanami 25-Apr-05 G 1400 35.5
1 Isanami 13-Apr-05 D 1400 44.5
1 Isanami 23-Mar-05 G 1200 35
1 Isanami 04-Mar-05 G 1000 35
1 Isanami 06-Nov-04 H 1000 41.5
1 Isanami 13-Oct-04 G 900 43.5
1 Isanami 04-Oct-04 D 800 0
1 Leveller 04-Feb-06 G 1400 45
1 Leveller 28-Jan-06 G 1410 48
1 Leveller 14-Jan-06 G 1400 45.5
1 Leveller 07-Jan-06 G 1200 45
1 Leveller 17-Dec-05 G 1100 39
1 Leveller 03-Sep-05 D 1100 41.5
1 Leveller 02-Apr-05 G 1200 51.5
1 Leveller 12-Mar-05 G 1200 51
1 Benko 11-Feb-06 G 1200 44.5
1 Benko 01-Feb-06 D 1000 46
1 Benko 29-Oct-05 G 2000 46.5
1 Benko 22-Oct-05 S 1600 47.5


Thanks in advance

Obewan

 

[Oorang]

You can do this with Vlookup or Match/Offset. But since your primary key is not column 1 I think match offset will be best suited to our task.

The first thing you will want is a unique list of all the horses. To get this, do the following:
Highlight the horse column of your download sheet.
Select data, filters, advanced filter. (You may get a "cannot determine label" message, just click OK)
Select copy to another location.
Press the red and white button to the far right of "Copy to"
Select a cell to the right of your data for the list to go to and press the red and white button again.
Check mark Unique records only.
Press OK.
This will generate a unique list of horses.
Cut and paste this list over to your summary sheet With the column header in Cell A1.

Then use the match/offset formula to retrieve
your data. So using your example above, and assuming your downloaded sheet is called "Data Sheet", in B2 put:

=OFFSET('Data Sheet'!$A$1,MATCH($A2,'Data Sheet'!$B$1:$B$44,0),COLUMN(A1)-1)

Then copy it four cells to the right and down as many horses are on your unique list.
This will pull in the first row for every horse.