Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the top 10 from a month of data
Hi,
I would like to get the top 10 most frequent occurrence of usernames from a month of data and their corresponding frequencies. The sample data: Time Notified Users 06:01 Yes user1 07:05 No user2 07:06 Yes user1 07:11 Yes user1 08:33 No user2 09:45 No user3 09:55 No user4 10:44 Yes user3 10:45 No user4 10:47 No user1 11:04 No user3 The workbook contains one worksheet each for everyday of a month and there are about 300 rows of data everyday. For example, for the month of September, there are 30 worksheets, namely 1, 2, 3, 4, 5.....30. There are about 200 possible users. I need to generate a report to rank the top 10 frequent users in the month for which 'Notified' is "No". What I have in mind is to Loop through all the worksheets and look down column 'Notified' then pick up the corresponding value of the 'Users' column, and put all these users value into an array (but only keep the unique ones). Then I iterate through the array and loop through all the worksheets again to count the number of occurrence of each array member, then put the result into a 2-dimensional array where first column is the user names, 2nd column the number of occurrence. Finally I just sort the 2nd column in the array to get the Top 10. Is there a better way to do it? If not, how to come about to work with these arrays? Is there somewhere I can see a sample of similar codes? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the top 10 from a month of data
I would use a pivot table using 'multiple consolidated ranges'
Go through the wizard and add all the days of the month to the consolidated range then you will be able to see each user with his frequency. you can even sort the field by the frequency and thus display the top 10. this method is quick and efficient and does not involve any macros. Post back here if you need further explanations "Hii Sing Chung" wrote in message ... Hi, I would like to get the top 10 most frequent occurrence of usernames from a month of data and their corresponding frequencies. The sample data: Time Notified Users 06:01 Yes user1 07:05 No user2 07:06 Yes user1 07:11 Yes user1 08:33 No user2 09:45 No user3 09:55 No user4 10:44 Yes user3 10:45 No user4 10:47 No user1 11:04 No user3 The workbook contains one worksheet each for everyday of a month and there are about 300 rows of data everyday. For example, for the month of September, there are 30 worksheets, namely 1, 2, 3, 4, 5.....30. There are about 200 possible users. I need to generate a report to rank the top 10 frequent users in the month for which 'Notified' is "No". What I have in mind is to Loop through all the worksheets and look down column 'Notified' then pick up the corresponding value of the 'Users' column, and put all these users value into an array (but only keep the unique ones). Then I iterate through the array and loop through all the worksheets again to count the number of occurrence of each array member, then put the result into a 2-dimensional array where first column is the user names, 2nd column the number of occurrence. Finally I just sort the 2nd column in the array to get the Top 10. Is there a better way to do it? If not, how to come about to work with these arrays? Is there somewhere I can see a sample of similar codes? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding the last day of the month | Excel Discussion (Misc queries) | |||
Finding Particular Day of Month | Excel Worksheet Functions | |||
Finding the last day of a month | Excel Discussion (Misc queries) | |||
Finding the Sum of value each month | Excel Discussion (Misc queries) | |||
Finding the Last Day in the month | Excel Programming |