You can do (almost) anything with a spreadsheet........

bowringi · 28 August 2013 12:35

Hi all,
Picking up on a comment from Jon, wondering if there's an Excel expert lurking out there. I have two Excel files, one a list of competitors, including a column for 'ISAF Sailor ID'; the other a much larger list of world rankings, also including a column for 'ISAF Sailor ID'. Is there a command, macro or similar that would, for the competitor list, look up the appropriate world ranking and add it in to a new ranking column in the competitor list? (Excel 2003 if it makes a difference).
Many thanks, Ian.

Sailwave · 28 August 2013 13:24

Hi Ian.

Just use the vlookup command

Jon

···

Jon Eskdale
Sailwave

03333 443377

07976 709777

On 28 August 2013 13:35, bowringi bowringi@eircom.net wrote:

Hi all,
Picking up on a comment from Jon, wondering if there’s an Excel expert lurking out there. I have two Excel files, one a list of competitors, including a column for ‘ISAF Sailor ID’; the other a much larger list of world rankings, also including a column for ‘ISAF Sailor ID’. Is there a command, macro or similar that would, for the competitor list, look up the appropriate world ranking and add it in to a new ranking column in the competitor list? (Excel 2003 if it makes a difference).

Many thanks, Ian.

MalcolmO · 28 August 2013 14:29

It would simplify matters if you copied the world rankings data into another worksheet in the list of competitors file.

regards,
Malcolm Osborne
Sedgefield South Africa

···

On 2013/08/28 15:24, Jon Eskdale wrote:

Hi Ian.

Just use the vlookup command

Jon

Jon Eskdale
Sailwave
03333 443377
07976 709777

On 28 August 2013 13:35, bowringi <bowringi@eircom.net > <mailto:bowringi@eircom.net>> wrote:

    Hi all,
    Picking up on a comment from Jon, wondering if there's an Excel
    expert lurking out there. I have two Excel files, one a list of
    competitors, including a column for 'ISAF Sailor ID'; the other a
    much larger list of world rankings, also including a column for
    'ISAF Sailor ID'. Is there a command, macro or similar that would,
    for the competitor list, look up the appropriate world ranking and
    add it in to a new ranking column in the competitor list? (Excel
    2003 if it makes a difference).
    Many thanks, Ian.

Rock_Kent · 28 August 2013 14:40

In general, I’m a big fan of the Match() and Index() functions in these situations.

MATCH(lookup_value, lookup_array, [match_type]) returns the position of the lookup_value in the Lookup_array. See full description here: http://office.microsoft.com/en-us/excel-help/match-function-HP010342679.aspx

INDEX(array, row_num, [column_num]) returns the value found in array at rown_num. See full description here: http://office.microsoft.com/en-us/excel-help/index-function-HP010342608.aspx

Together these have the advantage of not imposing any ordering requirements on the columns. Unlike vlookup(), you can search for a match in a column to the right of the column containing the desired value.

I normally using them in a single cell as

INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))

-rock-

···

On Wed, Aug 28, 2013 at 5:35 AM, bowringi bowringi@eircom.net wrote:

Hi all,
Picking up on a comment from Jon, wondering if there’s an Excel expert lurking out there. I have two Excel files, one a list of competitors, including a column for ‘ISAF Sailor ID’; the other a much larger list of world rankings, also including a column for ‘ISAF Sailor ID’. Is there a command, macro or similar that would, for the competitor list, look up the appropriate world ranking and add it in to a new ranking column in the competitor list? (Excel 2003 if it makes a difference).

Many thanks, Ian.

bowringi · 28 August 2013 14:44

Malcolm,

Thanks, once I got the steer on VLOOKUP, everything fell into place, including putting the ISAF rankings into a second worksheet. and moving the ISAF Sailor ID to Column 1. Then less than 5 mins later, all done. Thanks indeed, Ian.

···

From: Malcolm Osborne

Sent: Wednesday, August 28, 2013 3:29 PM

To: sailwave@yahoogroups.com

Subject: Re: [sailwave] You can do (almost) anything with a spreadsheet…