Complex Count
 

Hello
I would really appreciate some help here.
I want to count duplicate numbers based on a time spam.
This is what I have:

Call Date/Time Number Dialed Location Duration
09/09 22:37:36 17204355125 Denver, CO 813
09/09 22:25:24 17204355125 Denver, CO 111
09/09 22:09:09 17204355125 Denver, CO 951
09/09 22:07:08 17725710557 Sebastian, FL 57
09/09 21:03:20 13018467933 Frederick, MD 57
09/09 20:41:52 17725711815 Sebastian, FL 532
09/09 20:36:21 17725710557 Sebastian, FL 9
09/09 20:35:31 17725710557 Sebastian, FL 29
09/09 20:34:33 17725710418 Sebastian, FL 1

For example in this situation we have that 17725710557 and 17204355125 were
dialed 3 times each, but what I need is only to count as duplicate when the
same number is dialed 120 seconds or less from the time the previous call to
that number was over.

I know is a real tough situation, can it be done?

Regards
farid2001

Complex Count
 

Put a helper column to the right of your data and place the following
formula in the top data row and copy down to the last row.
(assumes date-time in column A and #phone in column B and that data is
sorted in #phone number then date-time descending order)

=IF(AND(B2=B3,A2-A3<=TIME(0,2,0)),"Dupe","NoDupe")

at top of column use something like (change column and range of data to
suit)

=COUNTIF(C2:C10,"Dupe")

To count the dupes

--

Regards,
Nigel




"farid2001" wrote in message
...
Hello
I would really appreciate some help here.
I want to count duplicate numbers based on a time spam.
This is what I have:

Call Date/Time Number Dialed Location Duration
09/09 22:37:36 17204355125 Denver, CO 813
09/09 22:25:24 17204355125 Denver, CO 111
09/09 22:09:09 17204355125 Denver, CO 951
09/09 22:07:08 17725710557 Sebastian, FL 57
09/09 21:03:20 13018467933 Frederick, MD 57
09/09 20:41:52 17725711815 Sebastian, FL 532
09/09 20:36:21 17725710557 Sebastian, FL 9
09/09 20:35:31 17725710557 Sebastian, FL 29
09/09 20:34:33 17725710418 Sebastian, FL 1

For example in this situation we have that 17725710557 and 17204355125
were
dialed 3 times each, but what I need is only to count as duplicate when
the
same number is dialed 120 seconds or less from the time the previous call
to
that number was over.

I know is a real tough situation, can it be done?

Regards
farid2001

Complex Count
 

Sounds easy to me. In fact, I don't think you even need a program for it;
you could do this with built-in Excel worksheet functions. Let's see he
You sort the data on number and timestamp. In a column to the right you have
this formula (assume the number is in column B and the timestamp in column A):

=IF(AND(RC2=R[-1]C2,RC1-R[-1]C1<TIME(0,2,0)),"DUP","")

This compares cols B and A to those of the previous row: If col B is the
same, and col A is less than 2 minutes later, it puts "DUP" in this cell;
otherwise it's blank. No program needed at all, so long as you sort the data
properly first.

If you WANT to write a program it can certainly be done; it would relieve
the user of sorting the data but would require him to run the program
instead. As usual, making it easier to use (by installing a button for the
user to click and writing the program) means taking more trouble yourself up
front. Which would you prefer?

--- "farid2001" wrote:
Call Date/Time Number Dialed Location Duration
09/09 22:37:36 17204355125 Denver, CO 813
09/09 22:25:24 17204355125 Denver, CO 111
09/09 22:09:09 17204355125 Denver, CO 951
09/09 22:07:08 17725710557 Sebastian, FL 57
09/09 21:03:20 13018467933 Frederick, MD 57
09/09 20:41:52 17725711815 Sebastian, FL 532
09/09 20:36:21 17725710557 Sebastian, FL 9
09/09 20:35:31 17725710557 Sebastian, FL 29
09/09 20:34:33 17725710418 Sebastian, FL 1

For example in this situation we have that 17725710557 and 17204355125 were
dialed 3 times each, but what I need is only to count as duplicate when the
same number is dialed 120 seconds or less from the time the previous call to
that number was over.

Complex Count
 

Hello Nigel

Thank you very much for your help, you guys are Excel Masters !!!

This is what I did:

39703.52331 115112324742 Peru-Lima 0.021527778 DUP
39703.54525 115112324742 Peru-Lima 0.000694444
39703.865 115112324742 Peru-Lima 0.001388889
39704.46297 115112324742 Peru-Lima 0.007638889
39704.79645 115112324742 Peru-Lima 0.006944444
39704.84566 115112324742 Peru-Lima 0.000694444
39704.8647 115112324742 Peru-Lima 0.004861111
39705.47488 115112324742 Peru-Lima 0.011805556 DUP
39705.48708 115112324742 Peru-Lima 0.003472222
39706.49169 115112324742 Peru-Lima 0.001909722 DUP
39706.49395 115112324742 Peru-Lima 0.000694444 DUP
39706.49535 115112324742 Peru-Lima 0.007638889

I converted both Call Date/Time and Duration columns into Values and after
sorting columns as you recomended I used following formula:

=IF(AND(B2=B3,A3-(A2+D2)<=0.001888),"DUP","")
and it worked perfectly!

Thanks & regards
farid2001

"Nigel" wrote:

Put a helper column to the right of your data and place the following
formula in the top data row and copy down to the last row.
(assumes date-time in column A and #phone in column B and that data is
sorted in #phone number then date-time descending order)

=IF(AND(B2=B3,A2-A3<=TIME(0,2,0)),"Dupe","NoDupe")

at top of column use something like (change column and range of data to
suit)

=COUNTIF(C2:C10,"Dupe")

To count the dupes

--

Regards,
Nigel




"farid2001" wrote in message
...
Hello
I would really appreciate some help here.
I want to count duplicate numbers based on a time spam.
This is what I have:

Call Date/Time Number Dialed Location Duration
09/09 22:37:36 17204355125 Denver, CO 813
09/09 22:25:24 17204355125 Denver, CO 111
09/09 22:09:09 17204355125 Denver, CO 951
09/09 22:07:08 17725710557 Sebastian, FL 57
09/09 21:03:20 13018467933 Frederick, MD 57
09/09 20:41:52 17725711815 Sebastian, FL 532
09/09 20:36:21 17725710557 Sebastian, FL 9
09/09 20:35:31 17725710557 Sebastian, FL 29
09/09 20:34:33 17725710418 Sebastian, FL 1

For example in this situation we have that 17725710557 and 17204355125
were
dialed 3 times each, but what I need is only to count as duplicate when
the
same number is dialed 120 seconds or less from the time the previous call
to
that number was over.

I know is a real tough situation, can it be done?

Regards
farid2001

Complex Count
 

Hello Bob

Thank you very much for your help, you guys are Excel Masters !!!

This is what I did:

39703.52331 115112324742 Peru-Lima 0.021527778 DUP
39703.54525 115112324742 Peru-Lima 0.000694444
39703.865 115112324742 Peru-Lima 0.001388889
39704.46297 115112324742 Peru-Lima 0.007638889
39704.79645 115112324742 Peru-Lima 0.006944444
39704.84566 115112324742 Peru-Lima 0.000694444
39704.8647 115112324742 Peru-Lima 0.004861111
39705.47488 115112324742 Peru-Lima 0.011805556 DUP
39705.48708 115112324742 Peru-Lima 0.003472222
39706.49169 115112324742 Peru-Lima 0.001909722 DUP
39706.49395 115112324742 Peru-Lima 0.000694444 DUP
39706.49535 115112324742 Peru-Lima 0.007638889

I converted both Call Date/Time and Duration columns into Values and after
sorting columns as you recomended I used following formula:

=IF(AND(B2=B3,A3-(A2+D2)<=0.001888),"DUP","")
and it worked perfectly!

I am going ahead with programing code in macro.

Thanks & regards
farid2001

"Bob Bridges" wrote:

Sounds easy to me. In fact, I don't think you even need a program for it;
you could do this with built-in Excel worksheet functions. Let's see he
You sort the data on number and timestamp. In a column to the right you have
this formula (assume the number is in column B and the timestamp in column A):

=IF(AND(RC2=R[-1]C2,RC1-R[-1]C1<TIME(0,2,0)),"DUP","")

This compares cols B and A to those of the previous row: If col B is the
same, and col A is less than 2 minutes later, it puts "DUP" in this cell;
otherwise it's blank. No program needed at all, so long as you sort the data
properly first.

If you WANT to write a program it can certainly be done; it would relieve
the user of sorting the data but would require him to run the program
instead. As usual, making it easier to use (by installing a button for the
user to click and writing the program) means taking more trouble yourself up
front. Which would you prefer?

--- "farid2001" wrote:
Call Date/Time Number Dialed Location Duration
09/09 22:37:36 17204355125 Denver, CO 813
09/09 22:25:24 17204355125 Denver, CO 111
09/09 22:09:09 17204355125 Denver, CO 951
09/09 22:07:08 17725710557 Sebastian, FL 57
09/09 21:03:20 13018467933 Frederick, MD 57
09/09 20:41:52 17725711815 Sebastian, FL 532
09/09 20:36:21 17725710557 Sebastian, FL 9
09/09 20:35:31 17725710557 Sebastian, FL 29
09/09 20:34:33 17725710418 Sebastian, FL 1

For example in this situation we have that 17725710557 and 17204355125 were
dialed 3 times each, but what I need is only to count as duplicate when the
same number is dialed 120 seconds or less from the time the previous call to
that number was over.