Selecting, sorting and ranking

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,011
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
 

Some videos you may like

Excel Facts

Links? Where??
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
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,169
Messages
5,570,638
Members
412,334
Latest member
ExcelForLifeDontHate
Top