How can you return the top 10 ascending text?

[newyorktimes]

Hi All,

I have a question:

There is a list that contains provinces, a name, and a number.

How can I find the top 10 names in descending order (highest num to lowest) for a specific province?

Eg. Column A
BC, ON, QC, BC, BC

Column B
Mary, Jane, Jack, Rudy, Hank

Column C
8,3,15, 20, 5

Column D
Top 3 for BC: Rudy, Mary, Hank

Thanks!

[oeldere]

use a pivot table for that kind of work.

[newyorktimes]

is there no formula? I would like to have this on a dashboard which automatically finds this information when data is updated without the manual manipulation of using a pivotable.

Thanks!

[JBeaucaire]

Put this ARRAY formula in D1, then copy down:

=INDEX($B$1:$B$5, MATCH(LARGE(IF($A$1:$A$5="BC", $C$1:$C$5, ""), ROW(A1)), $C$1:$C$5, 0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

http://screencast.com/t/oZKCAGdU2

[newyorktimes]

Put this ARRAY formula in D1, then copy down:

=INDEX($B$1:$B$5, MATCH(LARGE(IF($A$1:$A$5="BC", $C$1:$C$5, ""), ROW(A1)), $C$1:$C$5, 0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

http://screencast.com/t/oZKCAGdU2

I'm not sure why but when applying this to my data, I do not get the correct results. I am using an array, but it seems the order is not from the highest number (column C) to the lowest number. Any ideas?

Thanks again for the prompt response

[XOR LX]

Hi,

Can you post an actual workbook with an example and your expected results?

Difficult to be sure of what you want with your pasted data.

Regards

[newyorktimes]

UPDATED CORRECT FILE...seems there is no way to delete the old ones.

please let me know if there is a way to make the top 10 list dynamic with forumlas so if data changes in column A-C, it will be reflected within the Top 10 table.

Thanks!

[oeldere]

in a jpg we can;t work.

with an pivot table (as already) stated, you can get your desired result.

i believe the solution of JBeaucaire will als work.

[newyorktimes]

How can i post the workbook?

[JBeaucaire]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Your file must be smaller than 1mb, zip it up if needed.

[newyorktimes]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Your file must be smaller than 1mb, zip it up if needed.

Attached, thanks!

[oeldere]

with an pivot table.

see the attached file.

[newyorktimes]

with an pivot table.

see the attached file.

I understand this works as a pivot table, however the formatting does not quite suit my needs as I would like to create a single page dashboard - is there a modification to the existing formulas in the TOP 10 tab that can support this?

Thanks for the help,

[oeldere]

with 4 pivot tables

see the attached file.

[XOR LX]

Or, with a couple of array formulas**:

In G3:

=INDEX(DATA!$B$2:$B$51,MATCH(1,(DATA!$A$2:$A$51="QC")*(DATA!$C$2:$C$51='TOP 10'!H3),0))

In H3:

=LARGE(IF(DATA!$A$2:$A$51="QC",DATA!$C$2:$C$51),ROWS($1:1))

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[newyorktimes]

awesome thanks all!!

[oeldere]

Thanks for the reply.

Glad i could help.