-1

I've been trying to figure out a way to combine data from multiple tables into one pivot table. The data from the table is populated when certain information is copy and pasted from a system that we use in my business for multiple lines of production. The report I'm making for this will have multiple people putting in information to the tables (uniformly formatted though) so all of the computations need to be done on the back end.

How do I merge two tables in Excel that have identical columns? <-- the pure excel solution in the bottom of this post is the best solution I've seen for my issue, but I have no idea how to format it or implement it for what I need. Specifically I have 32 named tables that I will be aggregated the data from, and my idea was to create 1 table in which the information from all of the tables is put together, and then to have a pivot table to organize the information from the summary table.

Any help or advice on this would be greatly appreciated!!!

Community
  • 1
Ponathjd
  • 1

1 Answers1

0

I would use Power Query for this. Power Query is a free Add-In from Microsoft for Excel 2010 and 2013, and is built into Excel 2016 on the Data ribbon, under Get & Transform.

Power Query can append data from Excel tables or named ranges. It will automatically line up data from different sources under consistent column headings. It can deliver the result to an Excel Table or directly into the Excel Data Model (Power Pivot), which can feed your Pivot Table with greater power and flexibility.

Mike Honey
  • 2,562
  • Thank you! it looks like this is exactly what I need. I do have one question about this though. Does Power Query automatically update from the tables as information is changed or added? I'm trying to play around with it to get it working but it doesn't seem to update the information in the appended table as I add information to the individual ones and refresh all. I'm sure I'm doing something incorrectly. – Ponathjd Mar 18 '16 at 16:56
  • Yes Refresh All should reload all the data. Exactly how are you doing your "refresh all"? Try the button on the Data ribbon. – Mike Honey Mar 20 '16 at 23:12
  • I haven't had a chance today to play around with it today but when I do I will update this comment to let you know if it worked.

    Essentially what I've got is multiple tables that are empty on a "master copy" workbook where multiple people are essentially copy+paste-ing information into their individual tables. I'm just trying to set it up so the person running the report will not have to do a lot of work to get the information needed and it's taken care of on the back end. If I append a bunch of blank tables together into one and just display the main one (where the rest are appended)....

     – Ponathjd Mar 21 '16 at 13:06
  • ...Then is that all I have to do to get it to work, then refresh all as information is added obviously? Does that all make sense? – Ponathjd Mar 21 '16 at 13:09
  • Yes, Power Query should work nicely for that. – Mike Honey Mar 22 '16 at 00:26