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

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

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