match 2 excel worksheets

Apr 22, 2009 at 6:54 PM
Hey all,
I have two Excel files each with one worksheet. I need to join these two on a column and create a new excel file.
I do not want the user to do this manually, I like to create a procedure for this.
The only way I can achieve this is to use PHPExcel, read the two excels match and spit out the result. this should be easy.
But it feels rather awkward, like jumping over the creek to fetch water.
Any suggestion how to simplify my solution is highly appreciated.
Developer
Apr 23, 2009 at 12:55 AM
Would it be possible to show some example with a few more details. It sounds like you want to copy some cell range from one workbook and paste it into another?

Apr 23, 2009 at 4:27 PM
Hey Koyama
I do not find an option to upload any sheets. But I have two sheets in separate Excel files.
I want sheet1 enforced with column c in sheet2 as column d in sheet1. I join the sheets on column A in both sheets.
This is basically a left join in SQL terms.
(select sheet1.a, sheet1.b, sheet1.c, sheet2.c
from sheet1 left join sheet2 on sheet2.a = sheet1.a)
Developer
Apr 24, 2009 at 10:44 PM
Edited Apr 24, 2009 at 10:45 PM
According to the PHPExcel philosophy this should be solved using Excel functions + PHPExcel's calculation engine.

1. Move the sheet from the second workbook into the first workbook. See related post how to do this.
http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=36984

2. Let's say your Sheet2 holds data in the range A1:C90. Then, in Sheet1, insert formulas looking like this:

=VLOOKUP(A1, 'Sheet2'!A1:C90, 3, FALSE)  // insert this formula in 'Sheet1'!D1
=VLOOKUP(A2, 'Sheet2'!A1:C90, 3, FALSE)  // insert this formula in 'Sheet1'!D2

3. Replace the formulas in Sheet1 column D with calculated values:

$cell->setValue($cell->getCalculatedValue())

4. Save the workbook.

Apr 28, 2009 at 11:36 AM
Hey again,
Thanks I like your solution and I try this during the weekend.
/Lasse :)