combine two files

[Guest]

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

 

[Dave O]

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.