Grouping data in excel 2007

[Madiya]

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

 

[Roger Govier]

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.
Data>Pivot Table>Finish
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 Area>Advanced>Sort>Descending>Using Filed>Count
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

 

[Madiya]

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.
Data>Pivot Table>Finish
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 Area>Advanced>Sort>Descending>Using Filed>Count
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











- Show quoted text -

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

Regards,
Madiya

 

[Roger Govier]

Hi Madiya

From Help on the Ceiling function
Returns number rounded up, away from zero, to the nearest multiple of
significance. For example, if you want to avoid using pennies in your prices
and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to
round prices up to the nearest nickel.

Syntax
CEILING(number,significance)
Number is the value you want to round.
Significance is the multiple to which you want to round.

My formula
If(C2=C1,""
Ignore if the document number is the same as the one on the row above
Else
CEILING(B2,1/1440*15)

There are 24 * 60 = 1440 minutes in a day, so we are rounding up to the
nearest 15 minute time interval.

 

[Madiya]

Hi Madiya

From Help on the Ceiling function
Returns number rounded up, away from zero, to the nearest multiple of
significance. For example, if you want to avoid using pennies in your prices
and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to
round prices up to the nearest nickel.

Syntax
CEILING(number,significance)
Number   is the value you want to round.
Significance   is the multiple to which you want to round.

My formula
If(C2=C1,""
Ignore if the document number is the same as the one on the row above
Else
CEILING(B2,1/1440*15)

There are 24 * 60 = 1440 minutes in a day, so we are rounding up to the
nearest 15 minute time interval.

--
Regards
Roger Govier








- Show quoted text -

Thats new thing to learn.
Great!!!
Thanks.
Madiya