Need to sum rows from multiple tables with varying layouts

**Giger** · 07-09-2012, 11:25 AM

I have several tables that I need to sum and combine into one master table. The problem is that not all the tables have the same layout of products. How would you combine the varying tables in order to create a master table where the products are summed accurately from the varying input tables?

Here's an example of the layout (below and attached in Excel). Lastly, my apologies if this has been answered already but after looking through the forum for about 20 mins, I couldn't find a similar example.

CATEGORY 1 AMOUNT
Product A 5
Product B 8
Product G 11
Product H 3

CATEGORY 2 AMOUNT
Product B 14
Product C 5
Product E 6
Product H 12

CATEGORY 3 AMOUNT
Product A 20
Product C 14
Product D 3
Product G 5

example table.jpg

**MarvinP** · 07-09-2012, 11:37 AM

Hi Giger,

This is a job for Pivot Tables using Multiple Consolidation Ranges. I have attached your answer.
See http://www.contextures.com/xlPivot08.html
I needed to add the Icon for this old function onto my Quick Access Toolbar (QAT) to show the dialog.

**JBeaucaire** · 07-09-2012, 11:45 AM

A Pivot Table should make short work of this: http://screencast.com/t/cI6JT7hYaoz

**Giger** · 07-09-2012, 12:03 PM

GUYS -A HUGE thank you for your responses. I'm just dumfounded right now thinking "why didn't I think of that"?

Thank you guys for your fast, thorough and clear responses.
Jerry - I never even heard of screencast before so I'll probably even use it for some of my customers.

thanks!

**JBeaucaire** · 07-09-2012, 01:44 PM

Giger, the think you want to search for is JING, I use it for quick screenshots, it puts a link to that screenshot into your clipboard for easy posting/emailing. Works for videos, too.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.