UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Calculate Groups, Office 2013    
 
   
billwild
post Nov 18 2019, 04:14 PM
Post#1



Posts: 113
Joined: 4-June 02



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.
Attached File(s)
Attached File  medical_example.zip ( 7.74K )Number of downloads: 4
 

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
Larry Larsen
post Nov 18 2019, 04:39 PM
Post#2


UA Editor + Utterly Certified
Posts: 24,385
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

No expert..

Select BCD columns

Press Alt A..

Then Sort.. (Column B)

Then Data - SubTotal (select column C & D)

HTH's
thumbup.gif
Reason for edit: attached image..

Attached File(s)
Attached File  2019_11_18_21_43_24.jpg ( 150.37K )Number of downloads: 1
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Daniel_Stokley
post Nov 18 2019, 04:40 PM
Post#3



Posts: 332
Joined: 22-December 14
From: Grand Junction, CO, USA


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.

Attached File  2019_11_18_14_38_12.png ( 80.55K )Number of downloads: 3


HTH, Daniel


Go to the top of the page
 
Larry Larsen
post Nov 18 2019, 04:49 PM
Post#4


UA Editor + Utterly Certified
Posts: 24,385
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Spot on Daniel.... thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
billwild
post Nov 18 2019, 07:36 PM
Post#5



Posts: 113
Joined: 4-June 02



THANK YOU ALL!!!!

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
WildBird
post Nov 18 2019, 08:15 PM
Post#6


UtterAccess VIP
Posts: 3,671
Joined: 19-August 03
From: Auckland, Little Australia


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)



Attached File(s)
Attached File  Capture.JPG ( 44.01K )Number of downloads: 0
 

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
billwild
post Nov 19 2019, 09:13 AM
Post#7



Posts: 113
Joined: 4-June 02



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!

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
WildBird
post Nov 19 2019, 04:22 PM
Post#8


UtterAccess VIP
Posts: 3,671
Joined: 19-August 03
From: Auckland, Little Australia


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.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th December 2019 - 01:00 PM