Selecting, sorting and ranking

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,046
I have a list of the worlds countries in column A, in column B i have the year they were visited the same in column C (if they have been visited twice) and in column D and so on.

What I would like to do is on sheet 2 select the top 10 countries that have been visited the most, what would be the best way to do this...I would like the country to appear (column A) along with the number of years (columns) the country has been visited appearing in column B in a descending order

Thanks

Paul
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
I've solved it in a fairly basic way by adding some extra columns to the left of your data:


Column A is headed "Ranking", Col B "Visits", Col C "Adj", Col D "To Rank", Col E "Country", Col F "Visit 1" and so on.

Start with Cell B2, type =COUNTA(F2:IV2) and copy down
Cell C2 enter value 0.001, cell C3 enter =C2+0.001 and copy down
Cell D2 is =SUM(B2:C2) and copy down
Cell A2 is =RANK(D2,D:D) and copy down

The reason for the adjustments of 0.001 is that you want to create unique values to rank and you can then use column A as the lookup value for the second sheet.

Hope that makes sense - haven't worked out how to post a worksheet on here yet!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,051
Messages
5,835,137
Members
430,343
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top