On Mar 10, 6:24*pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
Hi

One way.
Add an additional column to the right of your data title Time 2 with a
formula of
=IF(C2=C1,"",CEILING(B2,1/1440*15))

This will put unique documents into 15 minute time slots
Then, create a Pivot Table of the Data.
DataPivot TableFinish
Drag Date to Page field area
Drag Time2 to Row area
Drag Time2 again to the Data area as Count of Time2
Double click on Time2 in Row AreaAdvancedSortDescendingUsing FiledCount
of Time2
Use the dropdown arrow on Time2 to de-select Blanks.

Now, as you select different dates in the Page field, you will see a sorted
list of activity by 15 minute time slot
--
Regards
Roger Govier

"Madiya" wrote in message

...



I have data in following format
Date * * * * * * * *time * * * * * * * * *doc no * * * * * item
batch
(dd-mm-yyyy) * (hh:mm:ss)

10-03-2009 * * * 15.33.24 * * * * *1548526 * * * * abc * * * * aghdsa
10-03-2009 * * * 15.33.25 * * * * *1548556 * * * * abc * * * * aghdsa
10-03-2009 * * * 15.33.25 * * * * *1548556 * * * * xvd * * * * adddsa
10-03-2009 * * * 15.33.25 * * * * *1548556 * * * * xyz * * * * wwhdsa
10-03-2009 * * * 15.33.25 * * * * *1548556 * * * * def * * * * aghdjh
.
.
and so on for 24 hrs which is data of around 40 to 50K rows.

I need to segregate the data and know which time slot has max
activity.
Time slot can be 15 min.

In above example, doc no 1548556 should be counted only once.

Pl advise VBA approach or suitable formula for the same.

Regards,
Madiya- Hide quoted text -

- Show quoted text -

Roger Govier,
Thank you for your help. Works fine.
Can you pl explain how this formula works?

Regards,
Madiya