Merging data files - solved

**andrewlong** · 12-09-2010, 11:36 AM

I primarily use SPSS for my stats analysis, but am now working with numerous others who use Excel exclusively. I am somewhat proficient at Excel, but can't seem to figure this probably simple problem out.

I have a master file with columns of data (the first column being an ID number for the row). I have a second file with completely different columns of data, but that also contains that same ID numbers. I need to merge the files so that each row in the second file matches up with the correct row in the first file (done using the ID column). The problem is that the second file has only a small subset of the IDs (500 rows, compared to 3000 in the first file), so I can't use consolidate (to my knowledge at least). My first goal is merge the second file into the first file to create one large master file. My second, and real goal, is to just merge the necessary columns from the first file into the second file.

To better explain, a simple example file of what I am dealing with/looking for is attached.

**srschiller** · 12-09-2010, 11:57 AM

It sounds as if you want to add the columns from the first file, that match to ID numbers in the second file, to the second file. The VLOOKUP command cna do this either manually on a spreadsheet or automate in a VBA macro. You sill need a VLOOKUP statement for each column to be added. See Excel help for the usage of VLOOKUP. The only tricky part is that you need the ID numbers in the first file to be in column "A".

Good luck,

Stosh

**andrewlong** · 12-09-2010, 01:10 PM

thanks, this looks to be an adaquate fix for what I am doing... another quick question, is there a way for VLOOKUP to return a 0 or empty string instead of error text if the VLOOKUP cannot find the value it is looking up (using VLOOKUP in strict mode, of course). I have tried =IF(VLOOKUP(...),VLOOKUP(...),"") thinking the VLOOKUP in the logical test would parse to true if it found something and false if it did not, but that doesn't work.

**srschiller** · 12-09-2010, 04:22 PM

Since the syntax for the IFERROR statementis:

IFERROR(value,value_if_error)

How about the following:

IFERROR(VLOOKUP(........,......., etc.),0) for each column you wish to move. That should work.

Stosh