Merge data from two excel workbooks

[vjh]

Hi All,

I'm trying to automate a process I've been doing manually. Essentially, I have two source workbooks, I copy over two lists, merge them together, sorting and deleting duplicates.

A simplified view

Wbk1
ColA ColB ColC
Fred 35
Jack 40
Jane 45

Wbk2
ColA ColB ColC
Ella 10
Fred 15
Jane 20

And the desired result in a new workbook:

ColA ColB ColC
Ella 10
Fred 35 15
Jack 40
Jane 45 20


Any suggestions would be greatly appreciated!

... vj

 

[SkipVought]

vj,

I'd use MS Query.

First macro record copying each sheet into your workbook. Each sheet needs column headings like Name, Val1, Val2

Data/Get External Data/New database query - excel files -- your workbook -- one of the new sheets, [next],[next],[next], select the OPTION to EDIT, [FINiSH]

Now you're in the MS Query Editor. Add the other table (sheet), Join on Names, Sum Val1 from each table & Sum Val2 for each table.

File/return values to Excel.

VOLA!

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[PHV]

Sorry Skip but your suggestion is incomplete as it'll miss Ella and Jack in the posted sample.
As I don't think MS-Query supports FULL outer join, a way is to use an UNION query:
SELECT ColA, ColB, 0 As ColC FROM Wbk1
UNION SELECT ColA, 0, ColC FROM Wbk2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vjh]

Hey guys,

Thanks for your help on this... I'm almost there, but can't quite get the join query to work in Microsoft Query. I've never used it before, so bear with me!

Individually the queries return the desired result, but they won't join. I get an "error in the Join Clause" error.

Here's the query - I tried aliasing everything, but that didn't solve it.

SELECT Wk1.Location Loc, Wk1.`Employee Name` EN, Wk1.F3 F3, Wk1.F4 F4, Wk1.F5 F5, Wk1.F6 F6, Wk1.F7 F7, Wk1.F8 F8, Wk1.F9 F9, Wk1.`Total Hrs` TH, Wk1.REG RG, Wk1.`O#T#` OT, Wk1.`STAT N/E` SNE, Wk1.`STAT ELIG` SE, Wk1.STAT ST, Wk2.F16 F16, Wk2.F17 F17, Wk2.F18 F18, Wk2.F19 F19, Wk2.F20 F20, Wk2.F21 F21, Wk2.F22 F22, Wk2.`Total Hrs1` TH1, Wk2.REG1 RG1, Wk2.`O#T#1` OT1, Wk2.`STAT Worked N/E` SWNE, Wk2.`STAT WORKED` SW, Wk2.`REG STAT` RS
FROM {oj `TestQ`.Wk1 Wk1 LEFT OUTER JOIN `TestQ`.Wk2 Wk2 ON Wk1.`Employee Name` = Wk2.`Employee Name`}
UNION
SELECT Wk2.Location Loc, Wk2.`Employee Name` EN, Wk2.F3 F3, Wk2.F4 F4, Wk2.F5 F5, Wk2.F6 F6, Wk2.F7 F7, Wk2.F8 F8, Wk2.F9 F9, Wk2.`Total Hrs` TH, Wk2.REG RG, Wk2.`O#T#` OT, Wk2.`STAT N/E` SNE, Wk2.`STAT ELIG` SE, Wk2.STAT ST, Wk2.F16 F16, Wk2.F17 F17, Wk2.F18 F18, Wk2.F19 F19, Wk2.F20 F20, Wk2.F21 F21, Wk2.F22 F22, Wk2.`Total Hrs1` TH1, Wk2.REG1 RG1, Wk2.`O#T#1` OT1, Wk2.`STAT Worked N/E` SWNE, Wk2.`STAT WORKED` SW, Wk2.`REG STAT` RS
FROM {oj `TestQ`.Wk1 Wk1 LEFT OUTER JOIN `TestQ`.Wk2 Wk2 ON Wk1.`Employee Name` = Wk2.`Employee Name`}

Anything obvious I'm missing?

... vj