Forum Discussion
Please Help: Summarizing 4 Tabs into One Sheet for Ownership %, Investor Name, Quarter Info, Entity
My data is actually much bigger (more like 100+ columns/rows, and the percentage column is in columns L and M in most sheets, but not all) than what I shared here, and we receive this set of data from another team, so we are trying to develop a way to be able to keep the original tabs we received as is.
So, that being said, would there be a formula that we can use to come up with a summary tab?
Thank you!
Depending on how frequently those original sheets are received, how frequently you need to produce the desired summary, etc., etc., it strikes me there are at least two routes:
- Look into Power Query as a way to combine disparate layouts (I believe that could work, but am NOT conversant with that tool as of now)(or I think there's something called Power Pivot as well)
- Consider redesigning the process by which the information is assembled and combined so that the Pivot Table could still be the solution (I am conversant with getting people to re-think processes, even while recognizing it can take some time to do so)
I truly don't think that formulas per se are the solution to your situation. That's why you've been referred to the Pivot Table, but you're right that IT presumes a consistent layout. Nevertheless, I have the impression from what I've read that Power Query can be used (in effect) to create an intermediate level of coherence/consistency from which a Pivot Table or something resembling it can be formed. But I'll have to defer to those who know the tool.