Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Count Duplicate Numbers
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 56
Default Count Duplicate Numbers
Hello
I would really appreciate some help here.
I want to count duplicate numbers based on a time spam.
I've already got the duplicate dialed number Count using:
{=(COUNT(B2:B10))-SUM(IF(FREQUENCY(B2:B10,B2:B10)0,1))}
Also I am already able to get Duplicate dialed number Count by Location.

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 to count as duplicate is only if the
same number is dialed 120 seconds or less from the time the previous call to
that same number was over.

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

Regards
farid2001
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,766
Default Count Duplicate Numbers
Hi,

Assume your data is on range A1:E10. In cell B16, type 00:02:00. In A13,
type 17725710557. Now in B13, enter the following array formula

=SUMPRODUCT(($C$2:$C$10=$A13)*($B$2:$B$10-MIN(IF(($C$2:$C$10=$A13)*($B$2:$B$10),$B$2:$B$10)) =$B$16))

Enter 17204355125 in cell A14 and coy the formula down.

Please let me know how this works for you.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"farid2001" wrote in message
...
Hello
I would really appreciate some help here.
I want to count duplicate numbers based on a time spam.
I've already got the duplicate dialed number Count using:
{=(COUNT(B2:B10))-SUM(IF(FREQUENCY(B2:B10,B2:B10)0,1))}
Also I am already able to get Duplicate dialed number Count by Location.

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 to count as duplicate is only if the
same number is dialed 120 seconds or less from the time the previous call
to
that same number was over.

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

Regards
farid2001

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,646
Default Count Duplicate Numbers
Maybe =SUM(IF(B3:B11=B2:B10,IF(A3:A11-A2:A10<=120,1,0),0))
as an array formula confirmed with Ctrl+Shift+Enter.

Regards,
Stefi

€˛farid2001€¯ ezt Ć*rta:

Hello
I would really appreciate some help here.
I want to count duplicate numbers based on a time spam.
I've already got the duplicate dialed number Count using:
{=(COUNT(B2:B10))-SUM(IF(FREQUENCY(B2:B10,B2:B10)0,1))}
Also I am already able to get Duplicate dialed number Count by Location.

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 to count as duplicate is only if the
same number is dialed 120 seconds or less from the time the previous call to
that same number was over.

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

Regards
farid2001
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 56
Default Count Duplicate Numbers
Hello Stefi & Ashish

Thank you very much for your help, you guys are fantastic!!!

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 I used following formula:

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

Thanks & regards
farid2001

"Stefi" wrote:

Maybe =SUM(IF(B3:B11=B2:B10,IF(A3:A11-A2:A10<=120,1,0),0))
as an array formula confirmed with Ctrl+Shift+Enter.

Regards,
Stefi

€˛farid2001€¯ ezt Ć*rta:

Hello
I would really appreciate some help here.
I want to count duplicate numbers based on a time spam.
I've already got the duplicate dialed number Count using:
{=(COUNT(B2:B10))-SUM(IF(FREQUENCY(B2:B10,B2:B10)0,1))}
Also I am already able to get Duplicate dialed number Count by Location.

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 to count as duplicate is only if the
same number is dialed 120 seconds or less from the time the previous call to
that same number was over.

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

Regards
farid2001
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,646
Default Count Duplicate Numbers
You are welcome! Thanks for the feedback!
Stefi

€˛farid2001€¯ ezt Ć*rta:

Hello Stefi & Ashish

Thank you very much for your help, you guys are fantastic!!!

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 I used following formula:

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

Thanks & regards
farid2001

"Stefi" wrote:

Maybe =SUM(IF(B3:B11=B2:B10,IF(A3:A11-A2:A10<=120,1,0),0))
as an array formula confirmed with Ctrl+Shift+Enter.

Regards,
Stefi

€˛farid2001€¯ ezt Ć*rta:

Hello
I would really appreciate some help here.
I want to count duplicate numbers based on a time spam.
I've already got the duplicate dialed number Count using:
{=(COUNT(B2:B10))-SUM(IF(FREQUENCY(B2:B10,B2:B10)0,1))}
Also I am already able to get Duplicate dialed number Count by Location.

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 to count as duplicate is only if the
same number is dialed 120 seconds or less from the time the previous call to
that same number was over.

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

Regards
farid2001
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Duplicate Values Abdul Shakeel Excel Discussion (Misc queries) 2 February 29th 08 04:31 PM
How to count duplicate or repeat values Roshlin Excel Discussion (Misc queries) 12 January 28th 08 10:55 PM
count duplicate entries in column khilari Excel Discussion (Misc queries) 3 April 17th 06 09:56 PM
How do I count a row of NON-duplicate entries in Excell? needhelp Excel Worksheet Functions 1 March 20th 05 05:34 PM
get count of row without duplicate entries Aananth Excel Worksheet Functions 2 October 29th 04 06:09 PM


All times are GMT +1. The time now is 06:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement