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. |
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 |