CODEWITHSUNDEEP
excel
pod6
pod6

Reputation: 3

Combine data from multiple excel spreadsheets

I have been collecting data for a 3-stage research project. Unfortunately, the data for each stage is stored in a different page of a spreadsheet (not my doing, and there isn't anything I can do about it). However, to analyze the data, I need to combine all of the data into a single spreadsheet page, adding the information from the 2nd and 3rd page of the spreadsheet to the end of the appropriate row in the first sheet. That is, I need to add a participant's responses in stage 2 and 3 to their responses in stage one. Due to attrition in each stage, not everyone appears in each spreadsheet. Is there an easier way to combine the data, aside from sorting each page alphabetically, matching the names up, and then copying and pasting the rows from the second and third spreadsheet into the first?

Upvotes: 0

Views: 3340

Answers (2)

datatoo
datatoo

Reputation: 2049

If you are more comfortable with formulas. Make sure the PIN# is in the far left column of each sheet, (assuming they are unique and not misspelled) I assume you have headers in row 1 and this formula reflects 1000 rows and $AX$1000 must be adjusted depending upon how many are in Sheet2 & Sheet3.

A2 is your first record. Make sure the $ signs are the way they are in these examples Starting at Sheet1 at column 113 (which is DI) type 

=VLOOKUP($A2,Sheet2!$A$2:$AX$1000,COLUMN()-111,FALSE)

drag the formula over to Column 162 (FF)

Do the same for the columns in Sheet3. Starting at Column 163 (which is FG)

=VLOOKUP($A2,Sheet3!$A$2:$AX$1000,COLUMN()-161,FALSE)

drag the formula over to Column 212 (HD)

Upvotes: 1

Eddie
Eddie

Reputation: 10138

PivotTable.

http://office.microsoft.com/en-us/excel-help/pivottable-reports-101-HA001034632.aspx

Upvotes: 0

Related Questions