|
|
To advertise on these forums, e-mail us. |
|
|
Thread Tools | Search this Thread | Display Modes |
#1
|
|||
|
|||
Calling all Excel Experts please,...!
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 |
#2
|
|||
|
|||
see how you go with the attached Paul
uses vlookup |
#3
|
|||
|
|||
file might help
|
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
You need to make sure your Database of horses are listed in alphabetical order.
|
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 Last edited by moeee : 28th September 2010 at 08:24 PM. Reason: added something |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 Last edited by ixlat0 : 28th September 2010 at 09:54 PM. |
Thread Tools | Search this Thread |
Display Modes | |
|
|