Calling all Excel Experts please,...!

[Pauls123]

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

[stugots]

see how you go with the attached Paul

uses vlookup

[stugots]

file might help

[Pauls123]

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

[moeee]

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

[Pauls123]

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

[moeee]

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

[Pauls123]

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

[Pauls123]

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

[ixlat0]

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!

[Pauls123]

hi there ixlat0. I just noticed your reply before I am about to hit the sack here. I'll have a close look at your reply in the morning, however I am 99% (not definately), sure that it has something to do with the original copying and pasting from the website of the sectional times in TVN. When I just type them out myself it works....!!!!

But when I try to apply any of this to a cell containing text or numbers that I have copied and pasted as above, it doesnt work.

To be continued,.....

Thanks for all your assistance, Paul

[stugots]

still having probs paul? if so upload an example of the prob & im happy to take a look

[Pauls123]

Hi Stugots, Thanks for your kind offer. I'm running a bit late here today, and about to head off to the bowling club for the day, another of my addictions, bowls that is. If you wish to send me an email during the day, I'll send you my file with some explanatory notes of what I do and trying to do. By the way I am using Excel 2010.

Thanks again, Paul

pauls at tsn.cc

[stugots]

email sent Paul

[Pauls123]

Hi there Stugots, Must have been some hiccup with my email addy, yours didnt come through today. My email address is

Pauls@tsn.cc.

And thanks ixlat0. I'll have a look at yours shortly and get back to you,

Paul

[stugots]

email sent Paul

[stugots]

if it still hasnt come through my email is -

roadagain65 at gmail dot com