I have a list of say 100 names in A1:A100 with duplicates
I'd like to display the top 5 by occurrence in B1:B5
Any thoughts? I know its possible through the auto filter function but i was hoping to do it with a formula or array.
I have a list of say 100 names in A1:A100 with duplicates
I'd like to display the top 5 by occurrence in B1:B5
Any thoughts? I know its possible through the auto filter function but i was hoping to do it with a formula or array.
have you consider the function Rank
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I'm not too familiar with rank but i understand its basic operation. The logic in my head would be a countif function to count the number of occurrences in the range, rank them from one to five, then display the corresponding names as 1st, 2nd, 3rd, 4th, 5th. Can all this be done with rank?
1. It would help to see the workbook.
You get better help on your question if you add a small excel file, without confidential information.
Please also add manualy the expected result in your file.
To Attach a File:
1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
I dont have a complete list yet so i put together a simple on with only ten names.
My desired output is in column C. i just realized in making this that i would end up in cases where i have ties. I guess ties would look good displayed with a "/" between them?
or maybe just display the name and a number rank in the column next to it titled "Place" and stop at 5th place
See if the attached file, solves your question.
The result is in the green cells.
I used several helpcolumns to get the result.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks