Sorting question

[elliottcor]

Hi all...

Looking for some tips. I have 40,000 lines of data I need to scrub and summarize. So here's the deal. Im sure it's pretty simple but I just can't wrap my head around the best way to sort this out. I'm basically looking for the following:

For each firm I need to summarize the total number of IDs for each branch and the value of each share class for each branch. Do i have to do mutlple sub totals? The subtotal function is driving me nuts. Any suggestions on how to manage these subtotals for 40,000 lines would be super appreciated.

[MarvinP]

Hi Elliott,

This looks like a Pivot Table answer. NO Formulas NEEDED!!! All you need to do is drag and drop column names into boxes. See the attached for what may be what you want. Read about Pivot Tables. Look at a few YouTube videos and you should do this problem in short time.

PT for Elliott.xlsx

[elliottcor]

This is perfect. Thx Marvin! Quick question...is there a way to lock in formatting after you are done? For example if I have a Column as a currency...when I expand the subset the formatting reverts to General.

[MarvinP]

Hi Elliott,

I think you need to expand to the column that is showing a General, Right Click on it and then select "Value Field Settings". At the bottom of the Value Field Settings dialog is a "Number Format" button. Why they put it down there, I have no idea, but that is what you want to click on, to change from General to Currency.

If you like these answers click on the "Add Reputation" star below the post. That lets me know it worked and makes it easier to find this question in the future.