Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change font size in drop down validation lists | Excel Discussion (Misc queries) | |||
How do I increase type size in an Excel drop down list? | Excel Discussion (Misc queries) | |||
maximum size of a Microsoft Excel list for a pivot table | Excel Discussion (Misc queries) | |||
Changing font size in a list box | Excel Discussion (Misc queries) | |||
how to increase the font size in a dropdown list? | Excel Worksheet Functions |