Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
File extensions | Excel Discussion (Misc queries) | |||
.doc extensions | Excel Discussion (Misc queries) | |||
.doc extensions | Excel Discussion (Misc queries) | |||
What tab includes the most commonly-used features and commands | Excel Discussion (Misc queries) | |||
Review commonly used add-ins | Excel Discussion (Misc queries) |