Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging files with nearly identical values in one column
I've got two files of teacher's class information that I need to merge. For
instance, in Column C includes the teachers' names in both files. however, in FILE1 the names are listed lastname firstname (with no comma) while in FILE2, the names are listed lastname, firstname (with comma). Of course, the last names are not the same length from row to row. I need to get the column D information from File1 and have it show up in Column D in File2. normally, this would be a simple process of copying and pasting the information except that there are some names that appear in one file and not both and some teachers are listed on multiple rows in one file and a different number of rows in the other file. Any assistance would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging files with nearly identical values in one column
Insert a column AFTER Column C in file1 and enter this in the new D1
=SUBSTITUTE(C1," ",", ") and copy down... Now in new E1 of file2 enter this =VLOOKUP(D1, switch to file1, select column C and D both together then type ,2,false) and press enter... You can copy it down... You will get #N/A for names not found in file 1.. You can hide that with =IF(ISNA(Vlookup(),"",Vlookup()) Once you are happy, you can copy everything, paste special as values and delete either Col C or Col D, as you want. replace Vlookup() with the Vlookup you got above "Allen" wrote: I've got two files of teacher's class information that I need to merge. For instance, in Column C includes the teachers' names in both files. however, in FILE1 the names are listed lastname firstname (with no comma) while in FILE2, the names are listed lastname, firstname (with comma). Of course, the last names are not the same length from row to row. I need to get the column D information from File1 and have it show up in Column D in File2. normally, this would be a simple process of copying and pasting the information except that there are some names that appear in one file and not both and some teachers are listed on multiple rows in one file and a different number of rows in the other file. Any assistance would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging files with nearly identical values in one column
Thanks for the quick response. I now have another question related to
VLookup...is it possible to run it, somewhat, in reverse. In other words, in the same file (file2 in the previous question) I have building level course numbers (for instance M6151, M7151 and M8151) that I need to lookup. If the course number (in column D for instance) matches ANY THREE OF THOSE, I need to return a common number (054800...a state-level number this time) in column E where the forumula would be. I can setup a separate worksheet where I have all of the state-level numbers in column A with the various building-level numbers in the subsequent columns (some of the rows would have up to 5 building-level codes for a specific state-level code). Thanks again. "Sheeloo" wrote: Insert a column AFTER Column C in file1 and enter this in the new D1 =SUBSTITUTE(C1," ",", ") and copy down... Now in new E1 of file2 enter this =VLOOKUP(D1, switch to file1, select column C and D both together then type ,2,false) and press enter... You can copy it down... You will get #N/A for names not found in file 1.. You can hide that with =IF(ISNA(Vlookup(),"",Vlookup()) Once you are happy, you can copy everything, paste special as values and delete either Col C or Col D, as you want. replace Vlookup() with the Vlookup you got above "Allen" wrote: I've got two files of teacher's class information that I need to merge. For instance, in Column C includes the teachers' names in both files. however, in FILE1 the names are listed lastname firstname (with no comma) while in FILE2, the names are listed lastname, firstname (with comma). Of course, the last names are not the same length from row to row. I need to get the column D information from File1 and have it show up in Column D in File2. normally, this would be a simple process of copying and pasting the information except that there are some names that appear in one file and not both and some teachers are listed on multiple rows in one file and a different number of rows in the other file. Any assistance would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging files with nearly identical values in one column
You are welcome.
It is best to set up 'building level course numbers' in the left most column in the lookup range and state level to the right... then it is straightforward lookup... "Allen" wrote: Thanks for the quick response. I now have another question related to VLookup...is it possible to run it, somewhat, in reverse. In other words, in the same file (file2 in the previous question) I have building level course numbers (for instance M6151, M7151 and M8151) that I need to lookup. If the course number (in column D for instance) matches ANY THREE OF THOSE, I need to return a common number (054800...a state-level number this time) in column E where the forumula would be. I can setup a separate worksheet where I have all of the state-level numbers in column A with the various building-level numbers in the subsequent columns (some of the rows would have up to 5 building-level codes for a specific state-level code). Thanks again. "Sheeloo" wrote: Insert a column AFTER Column C in file1 and enter this in the new D1 =SUBSTITUTE(C1," ",", ") and copy down... Now in new E1 of file2 enter this =VLOOKUP(D1, switch to file1, select column C and D both together then type ,2,false) and press enter... You can copy it down... You will get #N/A for names not found in file 1.. You can hide that with =IF(ISNA(Vlookup(),"",Vlookup()) Once you are happy, you can copy everything, paste special as values and delete either Col C or Col D, as you want. replace Vlookup() with the Vlookup you got above "Allen" wrote: I've got two files of teacher's class information that I need to merge. For instance, in Column C includes the teachers' names in both files. however, in FILE1 the names are listed lastname firstname (with no comma) while in FILE2, the names are listed lastname, firstname (with comma). Of course, the last names are not the same length from row to row. I need to get the column D information from File1 and have it show up in Column D in File2. normally, this would be a simple process of copying and pasting the information except that there are some names that appear in one file and not both and some teachers are listed on multiple rows in one file and a different number of rows in the other file. Any assistance would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging identical workbooks in Excel 2007 | Excel Discussion (Misc queries) | |||
Merging two sheets with non-identical data | Excel Discussion (Misc queries) | |||
Looking Up Datas when Key Values are Identical | Excel Worksheet Functions | |||
Lookup of identical values? | Excel Worksheet Functions | |||
Merging to identical Excel files | Excel Discussion (Misc queries) |