How to conditionally merge/join excel worksheets?
conditional-statements, excel, join, merge, vba
Solution
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) !
Problem
``` +---------+---------+ +---------+--------------+ +---------+-------------+ + country + widgets + + country + frammis rods + + country + comex gears + +---------+---------+ +---------+--------------+ +---------+-------------+ + alpha + 1 + + bravo + 8 + + charlie + 18 + + bravo + 3 + + charlie + 16 + + delta + 9 + + charlie + 7 + + delta + 32 + +---------+-------------+ + delta + 11 + +---------+--------------+ +---------+---------+ ``` I have several Excel worksheets like these ^^^ within the same spreadsheet. I'm trying to create a new worksheet that contains merged data showing only those countries with data in all the worksheet categories. E.g., ``` +---------+---------+--------------+-------------+ + country + widgets + frammis rods + comex gears + +---------+---------+--------------+-------------+ + charlie + 7 + 16 + 18 + + delta + 11 + 32 + 9 + +---------+---------+--------------+-------------+ ``` I'm looking for a fairly simple, flexible way of doing this as the various worksheets are updated / new worksheets are added. I know how to do this in MYSQL, but I'm not sure if its possible in excel. So, what do the experts have to say? Thx in advance. :)