Creating VBA Macro For Grouping and Summation by Account Code

I run monthly phone usage reports for my work, and I'm having trouble creating a macro that can autosum the duration of the call in column B in a 00:00:00 format by the account number listed in column J.

I've attached an example:

I appreciate any thoughts!

So if the account is 104 then you would want the sum of just those values in B?

Exactly, I just need to be able to separate the 00:00:00 values by account code, and then autosum all the values in that column. Should be pretty simple, but I'm not very good with excel. Getting better, just not quite there yet.

I'm working on it but for some reason the sum is always 00:00:00 and I can't figure out why. I have asked the real gurus on this site to lend a hand so be patient and I'm sure you'll get an answer soon.

kmstyf,

Could give this code a try:

Please Login or Register  to view this content.

kmstyf,

If you're interested in a non-vba solution, a pivot table can provide the same results. Unfortunately I can't seem to upload a file at the moment to show you what it looks like, though

The problem is the values in column B "look" like time values but are in fact, text strings.

1) Copy the cell C3 which has a value of 0 in it, handy
2) Highlight the values in column B
3) Select Edit > Paste Special > Add > OK

Now all the values are turned into time values...look like decimals right now.

4) Format those cells again as [h]:mm:ss and you'll get your original look back


Now you can SUM those values. Or you can use a SUMIF() to sum the values based on the codes in column J.

Hi JB
I looked at the OP's file for about an hour today trying to reformat those cells (Column B) and couldn't make it happen. Can you share with me what this does

3) Select Edit > Paste Special > Add > OK

I SEE what it does but what's it doing under the covers? Add???...don't know what it's doing.

If you add 0 to any number it leaves the number the same.

The Paste Special > ADD will attempt to add the number currently in memory to every selected cell, a very "special" paste, indeed!

Interestingly, if you have a "text" string that looks like a number, adding 0 to in this manner will actually convert it to a real Excel number. Then you can change the formatting since it is now a number, back to the TIME display of your choosing.


It is well worth your time to go through all the PASTE SPECIAL options and wrap your head around what they can do. In combination with the F5 > Special (select) those two tools can be very powerful and quick to use for transforming large amounts of data.

Hi kmstyf

Using JB's excellent tip, try this code

Please Login or Register  to view this content.

kmstyf,

I'm finally able to upload the example file showing the pivot table