Menu
+---------+---------+ +---------+--------------+ +---------+-------------+
+ 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. :)
257
On the Data tab, under Tools, click Consolidate. In the Function box, click the function that you want Excel to use to consolidate the data. In each source sheet, select your data, and then click Add.
In the Function box, click the summary function that you want Excel to use to consolidate the data. The default function is SUM. Select your data. Next, in the Reference box, click the Collapse button to shrink the panel and select the data in the worksheet.
You don't even have to open all of the workbooks you want to combine. With the master workbook open, go to the Ablebits Data tab > Merge group, and click Copy Sheets > Selected Sheets to one Workbook. In the Copy Worksheets dialog window, select the files (and optionally worksheets) you want to merge and click Next.
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) !
200
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
