Most commonly dialled extensions

[JDB]

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.

 

[Gary''s Student]

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

 

[Pete_UK]

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(Sheet1!$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

 

[JDB]

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]

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