Merging spreadsheets based on Variable "Index"

[jfclogston]

Hoping for help!

I have a spreadsheet with multiple case records (let's say A-Z) that each have a value - say 5 with the value A, 10 with the value B, etc . . .and I separately have an index of value A to value Z that has values corresponding to each. There are too many values to create an if statement. Is there a way to merge spreadsheets so each case record A, for example, gets the value assigned to it from my "index table" and so on. . . As you can see, this is NOT a one-to-one merge, since there are multiple cases with each value (A-Z) and one table with individual "index" rows (A-Z).

I am going to ultimately put these into STATA, maybe it is easier to do it there? But I would rather do it in excel so I have the spreadsheet with all my merged values.

Thank you very much, this is for my dissertation, I have 360,000 rows of data and multiple covariates I need to merge into these case records. . .ugh

Frankie

[JBeaucaire]

Click GO ADVANCED and use the paperclip icon to post up a sample from your workbook, 20-50 rows. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer.

[jfclogston]

Thank you for this response. . . but I can't see Go Advanced. Then I am still concerned about how to merge. If I'm adding one new column of data from my "index" to the existing large data set, based on the common variable (case ID). . .do I need to create a blank column to make room for the new data? Or will it appear in the last column in the data set?
Thank you thank you again. Maybe I wasn't clear in my original post and I apologize

[JBeaucaire]

As of now, no, I do not understand what you want. Which is why I asked you to create a smaller sample workbook with manually mocked up "RESULTS" from that sample data.

As for uploading that workbook to this site, that would require you use the more advanced post editor:

2012-05-05_1451.png

[jfclogston]

Sorry now I understand.

I have created an attachment workbook

The first worksheet "Baseline Data" shows that the variable Case ID/Precinct Number has several lines with the same value for Precinct Number.
The second worksheet "Data to Merge" shows for a given precinct number, there are corresponding values - I created two columns of variables that correspond "zip code" and "median value"
I would like to merge this data from the second worksheet onto the first (you can see where I labeled columns on the first worksheet where this data might transfer)

Your help is greatly appreciated.
My actual data set has 1200 precincts and each precinct has 300 lines of data with different vote totals. . .and each precinct needs to get assigned variables, including zip code and median value that I have by precinct. So I am looking for a fast merge

Thank you in advance!!! I really appreciate it.

[JBeaucaire]

Your sample is better, but doesn't show the expected answers on the Baseline Data columns G:H. What is the logic for determining the value that goes into F3 and G3? F55 & G55?

[jfclogston]

I am attaching a spreadsheet with an IF argument (for precinct values 1 to 4. . . I didn't go all the way down to 10/ make it longer because of course the problem is I can't do an if argument for 1200 cases). . .But I hope the IF formula and the expected results for precinct 1 to 4 show the logic of how I want to assign the values in the second worksheet to these columns.

Thanks again for help
Frankie

[JBeaucaire]

These are simple VLOOKUP formulas...

F3: =VLOOKUP($A3, 'DATA TO MERGE'!$A:$C, 2, 0)
G3: =VLOOKUP($A3, 'DATA TO MERGE'!$A:$C, 3, 0)

Then copy F3:G3 downward.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.