Home |
Search |
Today's Posts |
#1
|
|||
|
|||
combine two files
Hi All:
I have two files. One looks like: Faculty Course Tom ACC 122 Bill BUS 220 Charles CIS 891 The other one looks like: Course Student Grade ACC 122 Lisa A ACC 122 Nacy B ACC 122 Jim A BUS 220 Sue A BUS 220 Carla B CIS 891 Xu A CIS 891 Ena C CIS 891 Karen D Is there any way I can merge these two files, or actually I want to add faculty name to each student grade record. I know we can merge these two files but the key variable Course is duplicated in the second file. I could not use SPSS to merge them. I don't know Macro. Is there anyone who can help me out? Thanks a lot. Charles |
#2
|
|||
|
|||
I put your example data onto a single spreadsheet: the Faculty Course
headers are in cells A1 and B1; the data is in A2:B4. The Course Student Grade headers are in cells E1, F1, and G1; the data is in cells E2:G9. I entered a new header in D1, "Faculty" and entered this formula in D2: =INDEX($A$2:$B$4,MATCH(E2,$B$2:$B$4,0),1) Copy this formula down thru D9. This formula looks for a match between the entry in column E and the source data in column B, then returns the corresponding value in column A. It's important to note that a spelling error will create a mismatch: for instance, in your sample data, Faculty member Charles teaches "CIS 891" but the students are taking a course called "CIS 891". If I substitute a caret sign for spaces, Charles teaches "CIS^^891" but the students take "CIS^891" which do not match. The point being, be aware of typos, leading and trailing spaces, etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recently Used File List - 2002 Contains 'Temp' Files | Excel Discussion (Misc queries) | |||
merging my excel files | Excel Discussion (Misc queries) | |||
how to combine multiple files in ms excel | Excel Discussion (Misc queries) | |||
importing multiple text files URGENT!!! HELP | Excel Worksheet Functions | |||
Combine multiple Excel files into one master | Excel Discussion (Misc queries) |