Tabulate data for a time period

vij · Sep 14, 2011

Hi,
I have this Table with me listing out the following:
Date Time Phone number

I want to tabulate the result from the above list to find out how many calls were made from a particular phone number on a particular date at a time (grouped for every two hours).

I have given a sample data and the result expected format below.
Would request some one from the forum to help please.
I am using excel 2010.
Thanks,
Vij

<table border="0" cellpadding="0" cellspacing="0" width="767"><colgroup><col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:1462;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="3" width="40"> <col style="mso-width-source:userset;mso-width-alt:3584;width:74pt" width="98"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="width:48pt" span="3" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;width:78pt" height="17" width="104">Sample table</td> <td style="width:30pt" width="40">Sample
</td> <td style="width:92pt" width="123">Sample
</td> <td style="width:30pt" width="40">
</td> <td style="width:30pt" width="40">
</td> <td class="xl65" colspan="2" style="mso-ignore:colspan;width:104pt" width="138">Result format</td> <td style="width:68pt" width="90">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl71" style="height:12.75pt" height="17">

Date

</td> <td class="xl71" style="border-left:none">

Time

</td> <td class="xl72" style="border-left:none">

Phone Number

</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl74">

Date

</td> <td class="xl75" style="border-left:none">

Time between

</td> <td class="xl75" style="border-left:none">

00:01 to 02:00

</td> <td class="xl75" style="border-left:none">

02:01 to 04:00

</td> <td class="xl75" style="border-left:none">

04:01 to 06:00

</td> <td class="xl66" style="border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

10:22

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

96599383944

</td> <td class="xl66" style="border-top:none;border-left:none">

8

</td> <td class="xl66" style="border-top:none;border-left:none">

8

</td> <td class="xl66" style="border-top:none;border-left:none">

8

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

10:22

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

96565549552

</td> <td class="xl66" style="border-top:none;border-left:none">

229

</td> <td class="xl66" style="border-top:none;border-left:none">

229

</td> <td class="xl66" style="border-top:none;border-left:none">

229

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

10:22

</td> <td class="xl67" style="border-top:none;border-left:none">

00971554291023

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

96565654065

</td> <td class="xl66" style="border-top:none;border-left:none">

117

</td> <td class="xl66" style="border-top:none;border-left:none">

117

</td> <td class="xl66" style="border-top:none;border-left:none">

117

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

10:22

</td> <td class="xl67" style="border-top:none;border-left:none">

00971554291023

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

96566092119

</td> <td class="xl66" style="border-top:none;border-left:none">

3

</td> <td class="xl66" style="border-top:none;border-left:none">

3

</td> <td class="xl66" style="border-top:none;border-left:none">

3

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

10:24

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

9779841445257

</td> <td class="xl66" style="border-top:none;border-left:none">

1

</td> <td class="xl66" style="border-top:none;border-left:none">

1

</td> <td class="xl66" style="border-top:none;border-left:none">

1

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

10:24

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

971554291023

</td> <td class="xl66" style="border-top:none;border-left:none">

157

</td> <td class="xl66" style="border-top:none;border-left:none">

157

</td> <td class="xl66" style="border-top:none;border-left:none">

157

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

10:24

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

971502268784

</td> <td class="xl66" style="border-top:none;border-left:none">

21

</td> <td class="xl66" style="border-top:none;border-left:none">

21

</td> <td class="xl66" style="border-top:none;border-left:none">

21

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

19:14

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

971558895812

</td> <td class="xl66" style="border-top:none;border-left:none">

16

</td> <td class="xl66" style="border-top:none;border-left:none">

16

</td> <td class="xl66" style="border-top:none;border-left:none">

16

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

19:15

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

971552934601

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

19:15

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

971508662579

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

19:15

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

9715022668784

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

19:16

</td> <td class="xl67" style="border-top:none;border-left:none">

00971554291023

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl68" style="border-top:none;border-left:none">

9715022668784

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

19:16

</td> <td class="xl67" style="border-top:none;border-left:none">

00971554291023

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl70" style="border-top:none;border-left:none">

97455311887

</td> <td class="xl66" style="border-top:none;border-left:none">

4

</td> <td class="xl66" style="border-top:none;border-left:none">

4

</td> <td class="xl66" style="border-top:none;border-left:none">

4

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

19:25

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl70" style="border-top:none;border-left:none">

974664622672

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">

2

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

19:25

</td> <td class="xl67" style="border-top:none;border-left:none">

0096565549552

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl70" style="border-top:none;border-left:none">

97433672980

</td> <td class="xl66" style="border-top:none;border-left:none">

3

</td> <td class="xl66" style="border-top:none;border-left:none">

3

</td> <td class="xl66" style="border-top:none;border-left:none">

3

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

22:49

</td> <td class="xl67" style="border-top:none;border-left:none">

0096896412790

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl70" style="border-top:none;border-left:none">

96896412790

</td> <td class="xl66" style="border-top:none;border-left:none">

33

</td> <td class="xl66" style="border-top:none;border-left:none">

33

</td> <td class="xl66" style="border-top:none;border-left:none">

33

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">

22:49

</td> <td class="xl67" style="border-top:none;border-left:none">

0096896412790

</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl73" style="border-top:none">
</td> <td class="xl70" style="border-top:none;border-left:none">

96893601745

</td> <td class="xl66" style="border-top:none;border-left:none">

6

</td> <td class="xl66" style="border-top:none;border-left:none">

6

</td> <td class="xl66" style="border-top:none;border-left:none">

6

</td> <td class="xl66" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" align="center" height="17"> <td class="xl69" style="height:12.75pt;border-top:none" height="17">

01 August 2011

</td> <td class="xl73" style="border-top:none;border-left:none">