1

I have two named tables on separate Excel worksheets in the same Excel 2010 workbook. These tables contain the exact same header items. I could get the results I want by copying the data from one of the tables, pasting it at the bottom of the other and then making a pivot table off of that.

However, for business reasons, this is not a good option, and I instead need to create a pivot table that can produce this same result, while leaving the source data in the two separate tables.

I have tried using the legacy PivotTable and PivotChart Wizard with multiple consolidation ranges to do this, but I ended up with fields called "Row", "Column" and "Value" instead of fields for each of my header categories.

Here is what my data looks like. John's Deals and Sue's Deals are set up exactly the same. The Deal Manager is always John or Sue, matching the worksheet name. enter image description here

And here is the kind of pivot table I want to be able to make:

enter image description here

Thank you in advance for any help anyone can offer with this.

  • Upon reading more, I have found some possible solutions that involve establishing data connections to the two different sheets, as if they were external sources, in order to perform a Union. However, I have not been able to do this because the file I am originally working from is stored as an XML Spreadsheet 2003, and, when I try to establish a connection, I cannot access data other than the first tab. – Liza Feb 7 '15 at 3:25
  • possible duplicate of How do I merge two tables in Excel that have identical columns? – agtoever Feb 7 '15 at 6:30
  • See duplicate link and jump to the part where "Sheet3" is created. In that answer, two tables in one worksheet are merged, so this should work for you too. Good luck. – agtoever Feb 7 '15 at 6:34
  • Thanks agtoever. My hope is to avoid the step of having to create a combined table, because the two tables are already very large, and having to duplicate all the data would make the file size too big. In my ideal solution, there would be only three worksheets--the two tables and the pivot table referencing both of them. I am beginning to think, though, that this will not be possible without using external data connections and PowerPivot. – Liza Feb 7 '15 at 15:40
  • Nope. As far as I know this isn't possible. I recommend considering a database solution as you are reaching the functional limits of Excel and entering the realm of databases... – agtoever Feb 7 '15 at 15:43
2

Another option if you're running an up to date version of Excel is to use PowerPivot, which is an optional add in to the latest versions of Excel. It's a steep learning curve, and I'd recommend learning the basics (Excel is Fun is a good place to start). I had a similar issue in creating an easily updated log for several different teams which could then spit out a report that would cover both individual, team and group performance. Several different tables with different pieces of data on them but the same fields, one report required. It works like a charm!

  • This didn't actually work for me in the scenario I was dealing with, because I was actually doing this programmatically. But, in general, this would be the best option. – Liza Dec 27 '17 at 20:28
1

I know this is old, but it is easy to do.

  1. Open the Pivot table wizard.
  2. Select "I will create the page fields"
  3. Add the different ranges.
  4. Select 1 for how many page fields you want
  5. Click on the first range, and then type in the page field name you want. EG The sheet range "John's deals" call "John", the sheet range "Sue's deals" call "Sue".
  6. Finish the table as normal and then move the pivot labels to suit.
1

You can also do this using Power Query (add-in in Excel 2010 / 2013, built-in Excel 2016) to create data connections from your tables and then create a new Append Query that combines the two data connections into one table you can base your Pivot Table on.

  • This didn't actually work for me in the scenario I was dealing with, because I was actually doing this programmatically. But, in general, this would be the best option. – Liza Dec 27 '17 at 20:28
  • Can you programmatically use the consolidate multiple ranges feature from the Pivot table wizard per @user656646's answer? – NetMage Dec 27 '17 at 20:36
  • No, as per my original post, "I have tried using the legacy PivotTable and PivotChart Wizard with multiple consolidation ranges to do this, but I ended up with fields called "Row", "Column" and "Value" instead of fields for each of my header categories." However, I no longer need to do this, and I think that, for most purposes Power Query is the best answer. – Liza Dec 27 '17 at 21:03

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.