COUNTIF or ?

[RJW]

I am not sure what to use here. what I am trying to do is sort by Day and hour and name. The minutes arent needed, and what is hanging me up. The number of rows in the column can vary.as can the names like Frank and Bill. For instance, I would like to see how many Frank did in the hour of 14:00 (2pm-2:59) and do that for every hour of the day and get a total for that name, for every hour of each date. I hope I am making sense, and any help would really be appreciated!


Frank 12/30/2004 2:58
Frank 1/1/2005 22:52 
Frank 1/2/2005 11:33 
Bill 1/2/2005 22:46 
Frank 1/3/2005 13:30 
Frank 1/3/2005 14:21 
Frank 1/3/2005 14:59 
Bill 1/3/2005 16:40 
Frank 1/3/2005 22:02 
Frank 1/4/2005 11:48 
Frank 1/5/2005 10:37 
Bill 1/5/2005 13:58 

 

[RJW]

I agree 100% with you about the date and time.. I am in a position now where this job was passed down to me, and I didn't create the table on the sql server I am pulling from for this, or I would have done it that way. I could also try to autofilter besides doing a pivot. The person who did this previously would use autofilter, delete unwanted rows, select what they wanted, and copy and paste it into another sheet in excel, which would take hours for so much data. (around 40,000 lines) I want to make this quick and less painful do do.

I really respect anyone that knows so much about this, its alot harder than being a sys admin, at least to me. I had my first shot at pivot tables and charts last week and was given a week to do it, and somehow that was narrowed down to 1 day, and had to analyze 6 months worth of data for around a dozen people in that time.. but at least I managed to do it, and it worked out just fine.

Again, thanks for your help!

 

[tactps]

If you want to use the autofilter technique, you can automate it a little. Just record a macro doing all of the steps that you want, and then post it. I would be happy to review and make it more dunamic for you.

Regards