software

I have been using Excel to compile baseball statistics for a list of
192 players. My problem is that the website I use to generate the
statistics into excel only allows so many stats at a time, AND
generates them for nearly 2000 players. What I have been doing for
each report is comparing the the master list (192 players) with the new
larger list alphabetically, deleting all the players not among the 192,
and then copy/pasting once the lists are identical.

Needless to say this is very time consuming and annoying. I KNOW there
must be a way to let excel do all the work, even for as many stats at a
time as I have. Perhaps vlookup??

Any help on this would be greatly appreciated.You can use VLOOKUP( ) for this. The larger table of 2000 players is
searched for a unique field, such as player number or player ID. This
should be the first field in the table. You could use a name, as long
as this is unique (no duplicates).

In your own smaller table of 192 players you would also have a player
ID column - suppose this is column A in Sheet1, and the larger table
occupies A2:M2001 in Sheet2. Assume the first player ID is in A2 of
Sheet1, then this formula in B2:

=VLOOKUP($A2,Sheet2!A$2:M$2001,COLUMN(),0)

would give you the data from the second column of the larger table for
the player in row 2 of your table (assuming there is a match - you will
get #N/A otherwise). If you want to derive the other statistics for
that player, copy the formula out to M2. You could then copy cells
B2:M2 down to row 193 to return the information for all of your 192
players.

You could then fix the data by highlighting B2:M193, clicking lt;copygt;
then Edit | Paste Special | Values (check) OK then lt;Escgt;, and this will
allow you to delete Sheet2 and retain the data for your smaller table.

Hope this helps.

PeteThank you so much for responding, YOU ARE A LIFE SAVER!!

The only problem I ran into--which I quickly figured out--is that in
order for the formula to work, the columns must match on both
worksheets. In order to fix this I simply inserted blank columns into
the new worksheet so that the new information I was extracting matched
up in both.

Is there any way to avoid this as well??

But again, thank you so much!!

I am not entirely sure how to apply this however--my first try
Pete_UK wrote:
gt; You can use VLOOKUP( ) for this. The larger table of 2000 players is
gt; searched for a unique field, such as player number or player ID. This
gt; should be the first field in the table. You could use a name, as long
gt; as this is unique (no duplicates).
gt;
gt; In your own smaller table of 192 players you would also have a player
gt; ID column - suppose this is column A in Sheet1, and the larger table
gt; occupies A2:M2001 in Sheet2. Assume the first player ID is in A2 of
gt; Sheet1, then this formula in B2:
gt;
gt; =VLOOKUP($A2,Sheet2!A$2:M$2001,COLUMN(),0)
gt;
gt; would give you the data from the second column of the larger table for
gt; the player in row 2 of your table (assuming there is a match - you will
gt; get #N/A otherwise). If you want to derive the other statistics for
gt; that player, copy the formula out to M2. You could then copy cells
gt; B2:M2 down to row 193 to return the information for all of your 192
gt; players.
gt;
gt; You could then fix the data by highlighting B2:M193, clicking lt;copygt;
gt; then Edit | Paste Special | Values (check) OK then lt;Escgt;, and this will
gt; allow you to delete Sheet2 and retain the data for your smaller table.
gt;
gt; Hope this helps.
gt;
gt; PeteI'm not sure which worksheet is the quot;newquot; one - if it is your own, and
you do not use all of the data in the 2000-player sheet, then you can
hide the columns in your own sheet. Another version of the formula is:

=VLOOKUP($A2,Sheet2!$A$2:$M$2001,2,0), or
=VLOOKUP($A2,Sheet2!$A$2:$M$2001,3,0), or
=VLOOKUP($A2,Sheet2!$A$2:$M$2001,4,0) etc

as these will retrieve data from the 2nd, 3rd or 4th column of the
lookup table. The main problem here is that you can't copy the formula
across row 2 in the same way as the formula with COLUMN( ) in it, but
if you only want a few of the columns or if you want to miss some of
them out then it might be more convenient to put something like these
in appropriate cells on row 2 and then copy down.

Hope this helps.

PeteOnce again, thank you so much. As with every new trick I learn on
Excel, I tweaked until I understood how to make it work.Well, that's the best way to learn. Glad I was able to help.

PeteHi Steven,

Instead of using the names column directly from your imported data in
Sheet2, you would need to use an alias where the name gets translated
into one which you are bound to recognise in your shorter list.

To begin with, then, you need to build up an alias table - you could
copy your list of names for the 192 players to another sheet (say
Sheet3) in column B. In column A of this sheet you can have several
variations of each name, as indicated above with Pujols, Albert, so
your list might grow, let us say to row 600 - your alias table is thus
Sheet3!A$2:B$600.

Now, when you import your table of 2000 player's statistics into
Sheet2, you need to insert a new column B, and this is where you can
change the name in the imported table with the name in your alias table
(assuming the names are in column A) - put this formula in cell B2:

=IF(ISNA(VLOOKUP(A2,Sheet3!A$2:B$600,2,0)),A2,VLOO KUP(A2,Sheet3!A$2:B$600,2,0))

This will give you the alias name from your table if the name in the
imported table is found there, otherwise you will just have the
imported name.

Now you need to change the other VLOOKUP formulae in Sheet1 to look at
the name in column B of Sheet2 and not column A. The easiest way to
change these is to highlight the columns in Sheet1 with these formulae
in and use Find amp; Replace (CTRL-H). I had given you this formula in my
second posting:

=VLOOKUP($A2,Sheet2!$A$2:$M$2001,2,0)

so assuming you are using something similar you will need to:

Find: Sheet2!$A
Replace With: Sheet2!$B

That should do it. Hope it helps.

Pete