1

So lets say I have 3 different sheets each with information on them like this:

Sheet 1 Sheet2 Sheet 3
item1|2 item2|7 item1|4
item3|5 item3|6 item6|2
item9|7 item8|4 item7|8

The first part (code#) is a code for an item. The second number is the quantity of that item. What I need to do, is to be able to populate a master list which will pull the quantity of each item and sum them up. In the above case, the master list would look like this:

item1|6
item2|7
item3|11
item4|0
item5|0
item6|2
item7|8
item8|4
item9|7

I'm sorry if this is confusing but I really need some help!

pnuts
  • 58,317
  • 11
  • 87
  • 139
user2409871
  • 21
  • 1
  • 1
  • 3
  • 1
    You need to look at a Pivot Table with multiple consolidation ranges. Press `ALT & d` then `p` to call up the pivot table wizard. Select the `Multiple consolidation ranges` option and you're good to go. – Skip Intro May 22 '13 at 15:08

1 Answers1

0

Elaborating on @Skip Intro's solution:

  1. Ensure all three sheets have the same labels (say Item and Qty), preferably in Row1.
  2. Alt+D then P, select Multiple consolidation ranges and PivotTable, Next, Next.
  3. Select your range from Sheet1 (say A:B), Add, repeat for Sheet2 and Sheet3, Finish.
  4. Change Count of Value to Sum of Value, if required and might as well select PivotTable, PivotTable Options, Totals & Filters, untick (if necessary) Show grand totals for rows.
  5. If you don't want (blank) to show either filter it out or restrict the data ranges (at the start) to less than whole columns.
pnuts
  • 58,317
  • 11
  • 87
  • 139