Forum Discussion
Please Help: Summarizing 4 Tabs into One Sheet for Ownership %, Investor Name, Quarter Info, Entity
Hi, Could someone please help me set up a summary sheet to show a list of investor names with greater than 10% ownership in 2 entities? Please see the attached file for how the source data is set...
Hello,
Based on the Excel workbook attached, you don't need 4 sheet tabs.
Kindly combine all the data in the four sheets into a single sheet.
You can have the following columns in the single sheet:
Date, Investor Name, Entity Name, Month, Year, Ownership (with values)
Then, you can use Pivot Table to summarize the data.
To get the Ownership %, drag and drop Ownership into the PT Values area and Month into the Rows area.
To get the Quarter, click on Group Selection and check "Quarter" only
Then, right click on any value in the Sum of Ownership and select Show Values As % of Grand Total
That will be your Summary Sheet
Based on the Excel workbook attached, you don't need 4 sheet tabs.
Kindly combine all the data in the four sheets into a single sheet.
You can have the following columns in the single sheet:
Date, Investor Name, Entity Name, Month, Year, Ownership (with values)
Then, you can use Pivot Table to summarize the data.
To get the Ownership %, drag and drop Ownership into the PT Values area and Month into the Rows area.
To get the Quarter, click on Group Selection and check "Quarter" only
Then, right click on any value in the Sum of Ownership and select Show Values As % of Grand Total
That will be your Summary Sheet
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!