# Selecting, sorting and ranking

#### Pauljj

##### Well-known Member
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
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.

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!

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.

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.

### Which adblocker are you using?

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

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