How to conditionally merge/join excel worksheets?

First off, thanks to @MikeD for an AWESOME writeup on a solution using pivot tables. I did manage to get that working in Excel 2007.

However, I decided to use a SQL query since its MUCH faster. (Yes, you can use SQL to merge worksheets. Sweet!)

1) Data --> From Other Sources --> From Microsoft Query

2) Choose Data Source --> Excel Files* --> Leave "Use the Query Wizard to create/edit queries" Checked

3) Select Workbook

4) Choose the columns you want --> click next --> Click "OK" on the pop-up warning you of the need to setup a manual join

5) In the "Microsoft Query" window --> Table --> Joins

6) Click on the "Return Data" icon

7) Import Data --> CTL + Click the column headers of the duplicates (e.g., "Country2," "Country3") --> Right Click --> "Hide"

8) !

I recommend using a pivot table with multiple consolidation ranges. (At least in 2010) this works whether the sheets are in the same workbook or in different workbooks (see pic's)

Hint: in Excel 2010 press Alt-D, then P to access the 2003 like Pivot table wizard.

no page fields

don't forget to ask for SUM instead of COUNT (any value cell --> right click --> summarize values by)

To arrive at your final layout make an additional header line just above the Pivot table (dark blue A, B; C, ...), create an autofilter and filter for non-blank in all columns

ta-taaaaa!