software

I am currently working on an Economics undergrad thesis regarding the
NHL and I have run into a stumbling block that I think can be solved
with Excel. I currently have two lists: list A is a list of player
names in column A with a statistic measuring their value in column B.
List B also has a list of player names in column A with the player's
salary in column B. List A and B have most of the players in common but
some players in list A are not in list B and some players in list B are
not in list A. I want to create a superlist that only has the players
in both lists in it, followed by a column with the statistic measuring
their value and a column with their salary. Is this possible?
One more thing: One of the lists currently has the names as First Name
Last Name (no comma) whereas the other list has the names as First
Name, Last Name (comma followed by 3 spaces). I am guessing that
would have to be fixed before the sorting procedure can take place.

If someone could help me out with this, I would really appreciate and
you'd probably save me numerous hours of tedious work.

Thanks.--
zgall1
------------------------------------------------------------------------
zgall1's Profile: www.excelforum.com/member.php...oamp;userid=31652
View this thread: www.excelforum.com/showthread...hreadid=513410For the list that has First Name, Last Name (comma followed by 3
spaces), highlight this column then CTRL-H then in the Find what box
enter comma followed by 2 spaces, leave the Replace box empty and click
Replace all - that should correct that anomaly.

Insert a new worksheet and copy just the names from sheet 1 to column A
(from A2 onwards). Enter a heading in A1, eg quot;Player Namequot; and in B1
quot;Tempquot;. In B2 enter this formula:

=VLOOKUP(A2,'Sheet 2'!A$2:A$200,1,0)

where I have assumed that the data on sheet 2 occupies A2 to B200 -
adjust the sheet name and references as necessary. Copy this down for
as many names as you have in column A - where there is a match of names
you will see the name in column B, or #N/A for no matches. Highlight
column B and click Data | Filter | Autofilter (on), then from the
pull-down in B1 select #N/A at the bottom of the scrollable list - only
the names which do not match will be displayed. Highlight the rows
which are visible on screen and Edit | Delete Rows. You can now select
quot;Allquot; on the filter pull-down, and you are left with names that appear
on both lists. Delete column B.

The new B1 can have the heading quot;Valuequot; and C1 the heading quot;Salaryquot;. In
B2 you can enter the formula:

=VLOOKUP(A2,'Sheet 1'!A$2:B$250,2,0)

where I have assumed that your sheet 1 list has data that occupies A2
down to B250 - adjust to suit. In C2 you can enter the formula:

=VLOOKUP(A2,'Sheet 2'!A$2:B$200,2,0)

again, adjust references to suit. Format these 2 cells how you want
them, then copy them down for as many players as you have in your list.

Finally, you can fix these values - highlight columns B and C, click
lt;copygt;, then Edit | Paste Special | Values (check) then OK and lt;Escgt;.

You can now delete Sheets 1 and 2 and you have your superlist with your
Value and Salary together. Use File | Save As to save the file with a
different name, so that you still retain the original sheets should you
need to get at the other names.

Hope this helps.

Pete