Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
JDB JDB is offline
external usenet poster
 
Posts: 42
Default Most commonly dialled extensions

Hi,

I need to find, from a list of data, the top ten most commonly dialled
extensions and then what days of the week these most common extensions are
dialled. I have the data in three columns. A is Date, B is Day and C is
extension dialled. The list is some 16000 items long!

I am really struggling with this one so any help greatly appreciated!

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Most commonly dialled extensions

Consider using a Pivot table.

You can make a table that lists each extension uniquely and display the
number of times it appears in the list:

http://www.contextures.com/xlPivot07.html#Unique


--
Gary''s Student - gsnu200816


"JDB" wrote:

Hi,

I need to find, from a list of data, the top ten most commonly dialled
extensions and then what days of the week these most common extensions are
dialled. I have the data in three columns. A is Date, B is Day and C is
extension dialled. The list is some 16000 items long!

I am really struggling with this one so any help greatly appreciated!

Thanks.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Most commonly dialled extensions

I would suggest that you obtain a list of the unique extensions
dialled (in a separate sheet) and then you can use a COUNTIF formula
to obtain the number of calls to each extension. From here you can
then sort in reverse order of count to obtain the top 10, and then in
5 adjacent columns (or 7 if you include weekends) you can get a count
for each day using a SUMPRODUCT formula.

To obtain the unique list, insert a new worksheet (assume it is
Sheet2), then copy the extensions (plus the heading) from column C of
Sheet1 to column A of Sheet2. It is important that you have a header,
so insert a new row 1 if you don't and put Extn in A1. Then with the
data and heading highlighted, click on Data | Filter | Advanced
Filter, and in the pop-up you should select Unique Records Only, and
click on Copy to another location - put C1 in the Copy To box, then
click OK. Now you can delete columns A and B, so that your unique list
of extensions is in column A of Sheet2.

Put the word Count in B1 and this formula in B2:

=COUNTIF(Sheet1!C:C,A2)

then copy this down to cover your unique list. Now you can sort
columns A and B using B as the sort key in descending order, and if
you only want to retain the top 10 you can delete all the rows below
row 11.

Enter the days of the week, Monday, Tuesday, Wednesday etc in C1, D1,
E1 etc, and then put this formula in C2:

=SUMPRODUCT((Sheet1!$C$2:$C$16000=$A2)*(TEXT(Sheet 1!$A$2:$A
$16000,"mmmm")=C$1))

Note that you can't use full-column references with SUMPRODUCT (unless
you are using XL2007), so adjust the ranges here to suit your data,
then copy across to your other day columns. Then copy those 5 (or 7)
formulae down the rest of your top-10 list to get a count for each
day.

Hope this helps.

Pete

On Nov 26, 9:28*am, JDB wrote:
Hi,

I need to find, from a list of data, the top ten most commonly dialled
extensions and then what days of the week these most common extensions are
dialled. I have the data in three columns. A is Date, B is Day and C is
extension dialled. The list is some 16000 items long!

I am really struggling with this one so any help greatly appreciated!

Thanks.


  #4   Report Post  
Posted to microsoft.public.excel.misc
JDB JDB is offline
external usenet poster
 
Posts: 42
Default Most commonly dialled extensions

Pete,

Thanks for the this, the first part of this works fine, but I'm getting zero
values for the days of the week? I've checked the formula and I have entered
it correctly and used the range C2:C16118 which is the range of data on
sheet1. Any ideas?

Cheers.

"Pete_UK" wrote:

I would suggest that you obtain a list of the unique extensions
dialled (in a separate sheet) and then you can use a COUNTIF formula
to obtain the number of calls to each extension. From here you can
then sort in reverse order of count to obtain the top 10, and then in
5 adjacent columns (or 7 if you include weekends) you can get a count
for each day using a SUMPRODUCT formula.

To obtain the unique list, insert a new worksheet (assume it is
Sheet2), then copy the extensions (plus the heading) from column C of
Sheet1 to column A of Sheet2. It is important that you have a header,
so insert a new row 1 if you don't and put Extn in A1. Then with the
data and heading highlighted, click on Data | Filter | Advanced
Filter, and in the pop-up you should select Unique Records Only, and
click on Copy to another location - put C1 in the Copy To box, then
click OK. Now you can delete columns A and B, so that your unique list
of extensions is in column A of Sheet2.

Put the word Count in B1 and this formula in B2:

=COUNTIF(Sheet1!C:C,A2)

then copy this down to cover your unique list. Now you can sort
columns A and B using B as the sort key in descending order, and if
you only want to retain the top 10 you can delete all the rows below
row 11.

Enter the days of the week, Monday, Tuesday, Wednesday etc in C1, D1,
E1 etc, and then put this formula in C2:

=SUMPRODUCT((Sheet1!$C$2:$C$16000=$A2)*(TEXT(Sheet 1!$A$2:$A
$16000,"mmmm")=C$1))

Note that you can't use full-column references with SUMPRODUCT (unless
you are using XL2007), so adjust the ranges here to suit your data,
then copy across to your other day columns. Then copy those 5 (or 7)
formulae down the rest of your top-10 list to get a count for each
day.

Hope this helps.

Pete

On Nov 26, 9:28 am, JDB wrote:
Hi,

I need to find, from a list of data, the top ten most commonly dialled
extensions and then what days of the week these most common extensions are
dialled. I have the data in three columns. A is Date, B is Day and C is
extension dialled. The list is some 16000 items long!

I am really struggling with this one so any help greatly appreciated!

Thanks.



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Most commonly dialled extensions

Perhaps your dates are not proper dates, but I now notice that you
have a day column (B), so perhaps you can try this instead:

=SUMPRODUCT((Sheet1!$C$2:$C$16118=$A2)*(Sheet1!$B$ 2:$B$16118=C$1))

You need to ensure that the days you have in the header row match what
you have in column B of Sheet1 (i.e. you might have Mon, Tue, Wed etc.
instead of the full name).

Hope this helps.

Pete

On Nov 26, 11:37*am, JDB wrote:
Pete,

Thanks for the this, the first part of this works fine, but I'm getting zero
values for the days of the week? I've checked the formula and I have entered
it correctly and used the range C2:C16118 which is the range of data on
sheet1. Any ideas?

Cheers.



"Pete_UK" wrote:
I would suggest that you obtain a list of the unique extensions
dialled (in a separate sheet) and then you can use a COUNTIF formula
to obtain the number of calls to each extension. From here you can
then sort in reverse order of count to obtain the top 10, and then in
5 adjacent columns (or 7 if you include weekends) you can get a count
for each day using a SUMPRODUCT formula.


To obtain the unique list, insert a new worksheet (assume it is
Sheet2), then copy the extensions (plus the heading) from column C of
Sheet1 to column A of Sheet2. It is important that you have a header,
so insert a new row 1 if you don't and put Extn in A1. Then with the
data and heading highlighted, click on Data | Filter | Advanced
Filter, and in the pop-up you should select Unique Records Only, and
click on Copy to another location - put C1 in the Copy To box, then
click OK. Now you can delete columns A and B, so that your unique list
of extensions is in column A of Sheet2.


Put the word Count in B1 and this formula in B2:


=COUNTIF(Sheet1!C:C,A2)


then copy this down to cover your unique list. Now you can sort
columns A and B using B as the sort key in descending order, and if
you only want to retain the top 10 you can delete all the rows below
row 11.


Enter the days of the week, Monday, Tuesday, Wednesday etc in C1, D1,
E1 etc, and then put this formula in C2:


=SUMPRODUCT((Sheet1!$C$2:$C$16000=$A2)*(TEXT(Sheet 1!$A$2:$A
$16000,"mmmm")=C$1))


Note that you can't use full-column references with SUMPRODUCT (unless
you are using XL2007), so adjust the ranges here to suit your data,
then copy across to your other day columns. Then copy those 5 (or 7)
formulae down the rest of your top-10 list to get a count for each
day.


Hope this helps.


Pete


On Nov 26, 9:28 am, JDB wrote:
Hi,


I need to find, from a list of data, the top ten most commonly dialled
extensions and then what days of the week these most common extensions are
dialled. I have the data in three columns. A is Date, B is Day and C is
extension dialled. The list is some 16000 items long!


I am really struggling with this one so any help greatly appreciated!


Thanks.- Hide quoted text -


- Show quoted text -


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
File extensions colleen Excel Discussion (Misc queries) 3 September 17th 08 12:18 AM
.doc extensions nnn5 Excel Discussion (Misc queries) 0 December 19th 07 02:09 PM
.doc extensions nnn5 Excel Discussion (Misc queries) 2 December 19th 07 02:08 PM
What tab includes the most commonly-used features and commands Cecil M. Scott Excel Discussion (Misc queries) 1 September 24th 07 07:50 PM
Review commonly used add-ins aristotle Excel Discussion (Misc queries) 0 July 19th 05 12:33 PM


All times are GMT +1. The time now is 11:55 AM.

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

About Us

"It's about Microsoft Excel"