Call Top3 based on duplicate data and ranking info

Hi Experts!!

The below is a simple example of what I have on my spreadsheet... the difference is that the real one involves a Top300 and over 50 countries which are not usually the same

A B C D

8 M John USA
6 M Jack USA
1 M Jason USA
3 M Jim USA
9 M Joe USA
2 M Johnson CAN
7 M Jacob CAN
5 M Paul CAN
4 M Peter CAN


I need Excel to automatically call the top3 per country based on lowest ranking (column A) from a new tab that's already created.

There's no min or max of people per country, it could be 1 as well as the 300 (which won't happen, but still)... But if a country has less than 3, should not appear on the list.

Once the Top3 for each qualifying country is on the new Tab, should run an average in column D for each country.

Hope you can help me!!! Thank you so much!!!

xoxo

Thank you so much DonkeyOte!!!!

Had no idea about this Pivot thing!!! worked like a charm, now playing around with it!!

Thanks so much!!! you're awesome!!!

xoxo

I just found out and was about to post!! You are awesome! Thanks so much!!

xoxo!

One more question.... (sorry!)

Is there a way to combine 2 pivot tables once the filters are applied or a way to put up a table with the filtered results of 2 pivot tables? (I will need to do the same with 4 pivot tables)

(the second pivot table is the same ranking but for women... that's what the M stands for)

No, I need to create a table combining the filtered data I got from the pivots. (Bottom3 by country M/F and then add the Boys and Girls)..

Sorry for the delay, but got sick and was completely out of service :S

Attached is the ideal example of what I'm looking to achieve... you'll find it on the Summary tab.

Please let me know if it's clear or need further explanation...

Thanks so much!!!!

xoxo

I've been thinking... if it's not possible to retrieve the data from the pivot tables automatically, then what I could do is to manually copy and paste the values of both of the filtered pivot tables (men and women bottom3) into the new tab and make a table grabbing the data from there, but not sure how...

Here's the spreadsheet detailing how it would work, but I don't know how to implement it =S

I would really appreciate if you could help me!!

Thanks so much!!!

xoxo