Hockey Pool Problems - List management

[alistair.calder]

I am trying to create a database of NHL Players, in prep for an
upcoming hockey pool. I am tired of the Hockey Pool magazines and
online stats that only give you a very limited view.

However, I am dealing with another factor: this is a lifetime pool.
So, many of the players I want to import into my database are already
owned by other players, they have salary restrictions, and more.

So, what I have is about 6 lists of players in Excel format (another
for salaries, another for owner listings, another for free agents and 3
more for past-year stats (2001 - 2003)). The lists are not the same as
they are broken down by year or by owner and each list contains
different players.

So, to make it MUCH easier to manage, I want to get a master list of
names, apply a unique number to each name on the master list, and then
apply that number to each matching name on the 6 Excel spreadsheets.

My question is this: I can certainly create a master list with all of
the names on it, and apply a unique ID to each name. Is there a way I
can then use that master list (along with the associated ID) to search
and place and ID next to the correct names on the remaining lists?

Thanks
Alistair

 

[Biff]

Hi!

You should be able to do this using a lookup formula.

Once you have the master list with player and ID (separate cells) ie:

A1 = Mario Lemieux
B1 = 66

Assume this list is in a sheet named Master in the range A1:B1000.

Then, assume on your other sheets with the players listed in column A,
A1:An, use a lookup formula like this:

=VLOOKUP(A1,Master!A1:B1000,2,0)

Biff

 

[alistair.calder]

Wow, that works great! Thanks for that. It saved me a LOT of hassle.

Now perhaps you can answer another question that is likely in the
newbie range: I discovered that my list has a few duplicates (Sergei
Fedorov shows up twice for some reason). Is there a way to have Excel
examine a column and report on duplicates?

 

[alistair.calder]

Actually, it looks like I figured it out! Thanks for the VLOOKUP tip!

 

[Biff]

You're welcome. Thanks for the feedback!

Go Pens!

Biff

 

[alistair.calder]

I've discovered a problem I cannot seem to solve, though I know it is a
simple one.

I created the formula above and it worked great.... sort of. I have
copied that formula to all of the players on my first sheet that
require a player ID number. But the formula slides up and I need to
column_array to stay the same. How do I get that to work?

The first line looks like this: =VLOOKUP(A1,Master!A1:B1000,2,0)
The Second: =VLOOKUP(A1,Master!A2:B1001,2,0)
The Third: =VLOOKUP(A1,Master!A3:B1002,2,0)

 

[Sandy Mann]

If I follow you correctly you want to use absolute references like:

=VLOOKUP(A1,Master!$A$1:$B$1000,2,0)

which will keep the references the same as you drag the formula down using
the fill handle.

Look up Absolute References in the Help files. In XL 97 enter the question
in Help "Absolute Ranges" then select "Move or Copy a formula" then click on
the >> symbol

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk