If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A set of Excel 2003 problems
I have a need to compare two lists...a full list of all the club and
then a smaller list of club members with blue eyes. BOth lists have names and eye colors. However, one list was made by John who thinks that Mary Van Dahl needs a space between the last name segments and the other list was made by Carl who thinks that the space is extraneous and lists Mary VanDahl. Of course, a sort puts these two names in different places. How do I take the extra space out of John's list? And how do I completely make them similar by removing all the capital letters ... i.e. mary vandahl I want to do a manual sort by opening these two lists in completely separate windows...no, I don't want to use the "window" functions of Excel; for some reasons I have I want to open completely separate windows. I can't figure out how to open a second Excel sheet in a new window when there is already an Excel sheet open. Even better, I'd like to compare the two lists and have the data in John's list that has an equal in Carl's list put into a third sheet with all the full data that John keeps (address, phone number, etc.) that Carl does not. Thoughts? Jim |
#2
|
|||
|
|||
A set of Excel 2003 problems
1) To change all "Van space Name" to "VanName"
Select the range to be processed; used Edit | Find & Replace. In the From box type van with a single space after it; in the To box type van with no space; click Replace All Do the same with other 'titles' 2) To change all to lower case for sorting. Lets say the first name is in A1 Insert a new A column (right click on the A header and select Insert) In new A1 enter =LOWER(A1); copy down the column (fasters way is to double click A1's fill handle - solid square in lower right corner of active cell) Next we need to convert these formulas to values: select all of A; use Copy; now use Edit | Paste Special - Values (look for a box labeled Values in the dialog); click OK done For lots of info on looking a duplicate tables visit http://www.cpearson.com/Excel/Lists.htm best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RST Engineering" wrote in message ... I have a need to compare two lists...a full list of all the club and then a smaller list of club members with blue eyes. BOth lists have names and eye colors. However, one list was made by John who thinks that Mary Van Dahl needs a space between the last name segments and the other list was made by Carl who thinks that the space is extraneous and lists Mary VanDahl. Of course, a sort puts these two names in different places. How do I take the extra space out of John's list? And how do I completely make them similar by removing all the capital letters ... i.e. mary vandahl I want to do a manual sort by opening these two lists in completely separate windows...no, I don't want to use the "window" functions of Excel; for some reasons I have I want to open completely separate windows. I can't figure out how to open a second Excel sheet in a new window when there is already an Excel sheet open. Even better, I'd like to compare the two lists and have the data in John's list that has an equal in Carl's list put into a third sheet with all the full data that John keeps (address, phone number, etc.) that Carl does not. Thoughts? Jim |
#3
|
|||
|
|||
A set of Excel 2003 problems
Thank you, sir Jim On Thu, 21 Jan 2010 16:36:05 -0400, "Bernard Liengme" wrote: 1) To change all "Van space Name" to "VanName" Select the range to be processed; used Edit | Find & Replace. In the From box type van with a single space after it; in the To box type van with no space; click Replace All Do the same with other 'titles' 2) To change all to lower case for sorting. Lets say the first name is in A1 Insert a new A column (right click on the A header and select Insert) In new A1 enter =LOWER(A1); copy down the column (fasters way is to double click A1's fill handle - solid square in lower right corner of active cell) Next we need to convert these formulas to values: select all of A; use Copy; now use Edit | Paste Special - Values (look for a box labeled Values in the dialog); click OK done For lots of info on looking a duplicate tables visit http://www.cpearson.com/Excel/Lists.htm best wishes |
Thread Tools | |
Display Modes | |
|
|