# 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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### 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!

Replies
4
Views
45
Replies
4
Views
143
Replies
10
Views
181
Replies
2
Views
211
Replies
0
Views
89