Selecting, sorting and ranking

[Pauljj]

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

 

[Airfix9]

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!