Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Calculate Groups

Posted by: billwild Nov 18 2019, 04:14 PM

I have a spreadsheet that contains over 1,000 rows of medical expenses. I want to be able to have a macro that will group the providers (could be as many as 50 different ones) and then add a row that will show the totals of each of the numerical columns. I have attached a partial spreadsheet as an example.

 medical_example.zip ( 7.74K ): 7
 

Posted by: Larry Larsen Nov 18 2019, 04:39 PM

Hi

No expert..

Select BCD columns

Press Alt A..

Then Sort.. (Column B)

Then Data - SubTotal (select column C & D)

HTH's
thumbup.gif

 

Posted by: Daniel_Stokley Nov 18 2019, 04:40 PM

Hello,

Have you tried the built-in SubTotal tool? If not, make the Provider Name column the first column, then sort your data by the Provider Name. Then go to the Data menu and choose SubTotal. I have selected to do a Sum on both amount columns.



HTH, Daniel



Posted by: Larry Larsen Nov 18 2019, 04:49 PM

Spot on Daniel.... thumbup.gif

Posted by: billwild Nov 18 2019, 07:36 PM

THANK YOU ALL!!!!

Posted by: WildBird Nov 18 2019, 08:15 PM

Add headings, and pivot it. Date field is useless as is, if it is an actual date, can group on that, Excel has lots built in, or can roll your own (can get Year, Moth, Quarter etc easily enough)




 

Posted by: billwild Nov 19 2019, 09:13 AM

PERFECT! But is there a way to add a blank row after each total? I can do it manually but it takes forever with over 500 providers.

THANK YOU AGAIN!

Posted by: WildBird Nov 19 2019, 04:22 PM

If a pivot table doesnt have it built in - chjeck out the design and options - depends on what version you have as to what is available, otherwise you would need code to build something. Certainly doable via code, but pivot tables are easier.

Posted by: billwild Jan 3 2020, 04:48 PM

I'm still at a loss at how I can place a blank row after each category. I would also like to have all the totals 'BOLD'
Any help would be greatly appreciated. - Bill

Posted by: MadPiet Jan 3 2020, 07:26 PM

You can do this with DAX... kind of depends on what you're trying to do with the data, though. useful if you're doing data analysis, but otherwise, probably overkill.