There is most definitely a better way to do this. This is a quick an
dirty way to get the job done.
If you're reading this for the first time, it won't sound "quick an
dirty". After you do it a few times, it is.
This process uses worksheets within the same spread sheet.
There must be a common key field (column) in each of the tw
spreadsheets you want to combine.
Paste the first spreadsheet into a new spreadsheet in workboo
"Sheet1".
I suggest putting the key field in the A column, and sort the sheet i
ascending order by this field. If this is a problem, or you need t
retain the original sort order and don’t have another column you ca
sort by that will restore the sort order, I suggest inserting tw
columns at the beginning of your spreadsheet. In column B, simply pu
the numbers 1 through however many rows you have. When you are done
you can sort by this column and your original sort order will b
restored. Copy and paste the key field column into column A, and sor
the sheet by column A. Whatever you use as a key, the sheet must b
sorted in ascending order by that key. Not doing so may result i
inaccuracies. In whatever column the key exists in, it must be to th
left of the fields you want to merge. This is why I suggest copying th
column into column A. You can always delete later.
Go to "Sheet2". Paste the second spreadsheet here. Again, the key fiel
should be in column A, and the sheet sorted by this column. Follow th
recommendations above.
This example assumes a spreadsheet in "Sheet1" that is 100 rows lon
and 4 columns wide (columns A, B, C, and D). Again, column A is the ke
field, and is sorted ascending. Also assumed is that "Sheet2" is 10
rows long, and 4 columns wide (columns A, B, C, and D) and that colum
A is the key field, and is sorted ascending.
In this example, you want to copy the cells in columns B, C, and D, i
sheet2, to sheet1, columns E, F, and G, respectively, for each row
where the cell in column A sheet1, matches the cell in column
sheet2.
For example, sheet1, cell A5, matches sheet2, cell A50. So, cop
sheet2, cells B50 C50 and D50, to sheet 1 cells E5 F5 and G5.
This example will copy the cells for each match.
On sheet1, copy the following formula into the designated cell.
Sheet1, Cell E1, formula:
=VLOOKUP(A1,Sheet2!$A$1:$D$100,2,FALSE)
Sheet1, Cell F1, formula:
=VLOOKUP(A1,Sheet2!$A$1:$D$100,3,FALSE)
Sheet1, Cell G1, formula:
=VLOOKUP(A1,Sheet2!$A$1:$D$100,4,FALSE)
Copy and past the sheet1 cells from E1:G1 to E2:G100.
Your done.
This method works best if the source cells are formatted as "TEXT".
To keep your results, you'll need to copy and past the results to
plain text word processor, like notepad, to clear the formulas an
retain the data.
Here's an explanation of the process.
=VLOOKUP(A1,Sheet2!$A$1:$D$100,2,FALSE)
Part 1: A1
Part 2: ,Sheet2!$A$1:$D$100
Part 3: ,2
Part 4: ,FALSE
- Part 1, A2
Search for the value contained in this cell, this worksheet (Sheet1)
- Part 2:
Sheet2!
The place to search is not in this sheet, search in Sheet2. Th
exclamation denotes that it is a worksheet.
$A$1:$D$100
Table array to search. Search the cell range of A1 to D100. Why th
dollar sign ($)? This allows you to copy and paste this formula int
several cells without excel performing it's logic to change the cel
range based on the relative location of the cell you are posting too
In other words, yes I really do mean these cells literally. Don'
change them.
- Part 3: ,2
This is the number of the column of the cell, that you want to retur
the results of, relative to your search table array.
OK, what does that mean?
You searched Sheet2. VLOOKUP automatically searches the first colum
you specified. That's column A. When it finds a match in column A, i
will return the value of the cell you specify here. You searche
A1
100. That's A-D. So,
1 equals return the value of the cell in column A
2 equals return the value of the cell in column B
3 equals return the value of the cell in column C
4 equals return the value of the cell in column D
If you had searched D5:J20, then:
1 equals return the value of the cell in column D
2 equals return the value of the cell in column E
3 equals return the value of the cell in column F
4 equals return the value of the cell in column G
5 equals return the value of the cell in column H
6 equals return the value of the cell in column I
7 equals return the value of the cell in column J
Part 4: ,FALSE
Don't approximate, or return results of the best match. Only return
results if the key field matches exactly.
Hope this helps. If I would have had this information a long time ago,
it would have saved me a lot of time.
I suggest performing tests on smaller sheets to verify your expected
results before using this on any important data.
Here's a link with more information:
http://office.microsoft.com/en-us/excel/HP052093351033.aspx