Need help attempting to create a "top 10 list"

[blainerhett]

Hey guys,

I'm creating a dashboard and I'm trying to implement a "Worse 10" list. I'm stumped.

I have 5 different audit forms that contain different questions. Each form contains about 40 unique questions. I'm trying to set up a table where the user can see the top 10 worst questions for each form:

Audit 1 - Audit 2 - Audit 3 -, etc, etc.

The data will change constantly, maybe once or twice a week. I would like to just update the raw data and have the table do the rest.

Any ideas? I'll take references to other examples and I can handle the rest, I just need to be pointed to the right direction.

Thanks!!!

[daffodil11]

I use data dumps that autopopulate often.

You can use =RANK()+COUNTIF()-1 to generate nonduplicating ranks on scores.

Then somewhere else, you can use INDEX(..MATCH( to pull the match data for the top 10.

Assuming you had data in A1:A1000, you would rank it in B1 with =RANK(A1,$A$1:$A$1000)+COUNTIF($A$1:A1,A1)-1 and copy downwards

Then on sheet2, in A1:A10 you would number 1 to 10. B1 =INDEX(Sheet1!$A$1:$A$1000,MATCH($A1,Sheet1!$B$1:$B$1000,0)) and copy downwards to B10 to pull just the Top 10.

[Sam Capricci]

I do something similar. I have an event file that I update every month with a new month's data and delete the beginning month's data (18 months revolving). I had to add a lot of formulas, mostly concatenations and I do the counts by month and by unit and then rank the results using =RANK(A2,$A$2:$A$50,0)+COUNTIF($A$2:A2,A2)-1 then put in a section where I refer to the results of those ranks in a 1 to 10 order using a vlookup. Of course in the rank formula the 0 might be replaced by 1 if you want to rank them in ascending order. Everytime I update the data it refreshes the output.

[MarvinP]

Hi,

If you supply a sample of your data it would be a better answer.

1. If your data changes, and you have more or less rows then a DNR (Dynamic Named Range) seems appropriate. See:
http://www.ozgrid.com/Excel/DynamicRanges.htm
2. Conditional Formatting has Top and Bottom 10 built into it. See:
http://www.excel-easy.com/data-analy...ormatting.html

3. I like to use Pivot Tables and it also has this ability to filter....

With a sample of your data and its structure we could help much more.