merging 2 datasets in excel?

[bug_girl]

I hope someone here knows how to do this....
I am working with two BIG datasets of student data in Excel.

Each has a unique identifier (Example:"bug_girl") attached to a bunch of information. (major, degree program, favorite color, etc.)

One data set is a subset of the other--in other words, we don't know color information for everyone, although we do know the major of everyone.

Because there isn't an equal number of observations, I'm having trouble getting the two datasets to combine. Some students have missing data in the other data set, so it mucks the merge up.

(I could do it in SPSS, but our office hasn't kept the license up. )

In case I haven't made sense, here's a visual analogy. I need to combine these two things into one set, with just missing data for some students.

Data set A

b 445
c 556
d 243
e 271

Data Set B

b green
d orange

 

[a_unique_person]

I would try using Access. Excel is just good for a quick number hack, but you are trying to do a join here, and Access will do it very easily and quickly. You could write your own code to do the job.

 

[Wudang]

What aup said.

 

[merentha]

I assume you're trying to put them under different columns like:
b 445 green
c 556
d 243 orange
e 27

If you really need to work this in Excel, you can either use the VLOOKUP worksheet function, or write an Excel VB code to populate using the Range FIND method (this is different from the worksheet FIND function). For some strange reasons, if you need to access the VB help for the FIND method, type "findnext" instead of "find" in the search box and look under "Find Method" in the "See Also" popup for the FINDNEXT method.

 

[varwoche]

What AUP said.

But if you don't have Access and feel like an experiment... There's an ODBC driver for Excel spreadsheets that let's them be queried as if they were "real" databases, perhaps letting you join the tables without exporting them to a database first.

 

[Starthinker]

Not an expert in this area but couldn't you fill the blanks with zeros or some placeholder?

 

[bug_girl]

Egad. Well, clearly I need someone with more knowledge of Access or Excel than I have. The problem is, we don't know which observations are missing in the second data sheet, so we can't add zeroes.

Maybe I can find someone with SPSS.

 

[Jorghnassen]

I hate Excel too. A text file and R is all I need to play with datasets.

 

[bug_girl]

Excellent! convinced my boss he needs to get me my own SPSS license. Problem solved.

 

[Jorghnassen]

I hate programs for which you need to pay a license (and update it every other day) to use.

/freeloader