Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping data in excel 2007
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping data in excel 2007
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping data in excel 2007
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping data in excel 2007
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 "Madiya" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping data in excel 2007
On Mar 12, 1:14*pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: 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 "Madiya" wrote in message ... 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- Hide quoted text - - Show quoted text - Thats new thing to learn. Great!!! Thanks. Madiya |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouping above in Excel 2007 | Excel Worksheet Functions | |||
Grouping Rows in Excel 2007 | Excel Discussion (Misc queries) | |||
How do I change the Data Grouping options in Excel 2007? | Excel Discussion (Misc queries) | |||
Need help with grouping in Excel 2007 | Excel Worksheet Functions | |||
Need help with grouping in Excel 2007 | Excel Worksheet Functions |