Hi there,

I'm looking for a bit of help on "excel" please. Let's say I have rated every horse in every race at sydney and melbourne following each meeting on a saturday. I've given them all a numerical value which I have determined as a kg value following its run.

All of the horses go into column A and all the ratings go into column B, next to each horse. As you can imagine my columns will start to get very lengthy with hundreds of horses in them. Then next saturday comes along, say Race 1 at sydney and I want to apply my rating for each horse in the race (I wont have all of them, due to horses resuming, coming off provincials etc).

Can I say copy and paste the fields into this spreadsheet, it then finds each horse and allots its rating next to it, or is this asking to much,...!

I know I can do a "find" but that would mean doing each horse individually, is there any faster way.

Thanks and regards
Paul

see how you go with the attached Paul

uses vlookup

file might help

Attached Files

nags_ratings.xls (18.5 KB, 803 views)

thanks Stugots, I am playing around with your formula now. It works excellent on a simple calculation, but when I apply it to my spreadsheet I am getting some number (rating), that is not correct. When I do a fill down, that same incorrect number keeps coming up.

I'm still trying here,

Thanks again, Paul

You need to make sure your Database of horses are listed in alphabetical order.

thanks Moeee, Yes I've done a a-z sort of all the horses, but something is still not working right. Still getting some incorrect number (rating), when its run over a large number of horses. But in a simple example like Stugots sent me, it is working fine.

Thanks again, Paul

Try this Formula instead
Substitute the NEW for the OLD in the supplied Spreadsheet.

OLD =LOOKUP(F3,A$3:A$7,B$3:B$7)

NEW =VLOOKUP(F3,A$3:B$7,2)

And again, your database of animals must be in alphabetical order.

EDIT : The above Formula goes into cell G3 in the spreadsheet.
Copy this cell all the way down to G7

Hi Moeee, thanks again. I just tried that and still getting some number that is totally wrong. Once again it works fine in a simple small spreadsheet. I then thought that maybe it was due to some of my horses having the (NZ) after their name, so went through and deleted all those (NZ)'s.

Still this same incorrect number.

Thanks again, Paul

Hi again Moeee. When I retype about half a dozen of my horses and retype the ratings into another spreadsheet, this works fine, both yours and Stugots.

So I am thinking it must be the format of the names and numbers that I have copied and pasted from the site where I got these from originally. This being the TVN sectional time site. But as far as I can see the cells are just formatted as either general or numbers.

Must be a solution that I am missing,

Paul

paul123

you were correct to clean up the data to "only" include the horse name -- even a space bar input (which you can't see) will marr the output and you will see #N/A

i suggest sheet2 become your data base

copy all horse names in sheet2 column A
copy all ratings in sheet2 column B

in sheet1 paste the horse name in column A (leave a gap between races for clarity)

copy this formula in sheet1 columm B as far down as appropriate

=IF(A1="","",VLOOKUP(A1,Sheet2!A$1:B$1000,2,FALSE))

comment:
the VLOOKUP command as used here isn't case sensitive so don't worry abt that

you need only clean up column A and start again for a new meeting and it keeps your data safe

A$1:B$1000 = the size of you data base (eg 1000 horses) if there are less it doesn't matter it will grow

alphabetical order shouldn't be an issue but probably needed to keep your data base up-to-date

hope this helps

good luck!

__________________
never smile at a crocodile -- don't be taken in by his welcome grin