Finding the top 10 from a month of data

[Hii Sing Chung]

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.

 

[GerryGerry]

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