Can I use PowerPivot instead of Copy/Paste for this application?

[SEMMatt]

Hello,

I recently became aware of the PowerPivot Add-On and I'd like to know if I can use it to join 4 Excel Workbooks which have all of the same Columns, just different Dates and Metrics.

I know how to add them into the PowerPivot tool but the PivotTable comes out looking a bit awkward because, in a sense, it's still Pivoting 4 different worksheets. What I really want to do is combine them without using Copy and Paste.

Is this possible using PP?

[JieJenn]

I think you can. I have done it before. PowerPivot is just a different type of MS Access.. (I am fully aware some people will attack me on the statement I just made)

[SEMMatt]

Thanks JieJen. Of course I won't attack you

Can you provide any more detail as to how exactly that's done?

[JieJenn]

In PowerPivot, if I remembered correctly, I imported all the workbooks and there was a function that let you consolidate data. Can't remember clearly, and I am using 2007 at work. It can be done easily in Access. If you have access, I can probably get it done for you with 15 minutes.

[SEMMatt]

I'm still a beginner at Access but just out of curiosity, in case I want to give it a shot, what's the way to do it?

[JieJenn]

You import all the worksheet into a MS Access Database, then you do a left joint for all 4 worksheets based on the common field. Kinda like a Vlookup but across all worksheets.