Forum Discussion
sum index match from different tabs into a consolidated tab
I would always suggest having a single consolidated database as the first step for something like this. So that's what I've created. (It is always easier for both you, the user, and Excel to work from a consolidated database. It is easier--simpler formulas--to parse data into those separate categories, for example, via formula or Pivot Table, rather than separating them at the start (as we would have done back in the pencil and paper days, on ledger sheets) .
From that consolidated database it's very easy to take one of two different approaches to summarizing the data.
- Perhaps the easiest is to use the Pivot Table, which will allow you to be selective, if desired, by product category (see the drop down selection at the top of the Pivot Table tab).
- The other is a formula that SUMs FILTERed results for each corporation for each column. You'll see that formula in your original "Top Customer -- Console" tab
I don't pretend that this is the complete answer -- the results aren't sorted based on highest quantities -- but it's a start and gives you an introduction to two quite different ways to approach the situation.
It would be a LOT easier to help you if, in addition to your verbal description of what you have and what you want, you could actually post a copy of the actual workbook. Recognizing that the actual workbook might well include confidential information--real names of real customers for example--if you could instead post a copy of a facsimile of the actual, in which you've replaced real names with fictitious ones.
Post it on OneDrive or GoogleDrive with a link here that grants access.
I would always suggest having a single consolidated database as the first step for something like this. So that's what I've created. (It is always easier for both you, the user, and Excel to work from a consolidated database. It is easier--simpler formulas--to parse data into those separate categories, for example, via formula or Pivot Table, rather than separating them at the start (as we would have done back in the pencil and paper days, on ledger sheets) .
From that consolidated database it's very easy to take one of two different approaches to summarizing the data.
- Perhaps the easiest is to use the Pivot Table, which will allow you to be selective, if desired, by product category (see the drop down selection at the top of the Pivot Table tab).
- The other is a formula that SUMs FILTERed results for each corporation for each column. You'll see that formula in your original "Top Customer -- Console" tab
I don't pretend that this is the complete answer -- the results aren't sorted based on highest quantities -- but it's a start and gives you an introduction to two quite different ways to approach the situation.
- Thank you, this is very helpful and intuitive.