Matching Lists Where List Size is Unequal
 

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: http://www.excelforum.com/member.php...o&userid=31652
View this thread: http://www.excelforum.com/showthread...hreadid=513410

Matching Lists Where List Size is Unequal
 

For 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 "Player Name" and in B1
"Temp". 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
"All" 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 "Value" and C1 the heading "Salary". 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
<copy, then Edit | Paste Special | Values (check) then OK and <Esc.

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