filter a list on top-3 values

[robertvdb]

I have a list of countries, and for each country the major cities (based on population). Now I want to filter this list, so that for each country only the top 3 major cities appear.

Any help is appreciated.

 

[kevin9999]

Would you be open to a helper column? If so, how about the following:

Book1
	A	B	C	D
1	Country	City	Population	Helper
2	country01	city01	1,000,000	6
3	country01	city02	1,100,000	5
4	country01	city03	1,200,000	4
5	country01	city04	1,300,000	3
6	country01	city05	1,400,000	2
7	country01	city06	1,500,000	1
8	country02	city01	2,000,000	6
9	country02	city02	2,100,000	5
10	country02	city03	2,200,000	4
11	country02	city04	2,300,000	3
12	country02	city05	2,400,000	2
13	country02	city06	2,500,000	1
14	country03	city01	3,000,000	6
15	country03	city02	3,100,000	5
16	country03	city03	3,200,000	4
17	country03	city04	3,300,000	3
18	country03	city05	3,400,000	2
19	country03	city06	3,500,000	1
Sheet1

Cell Formulas
Range	Formula
D2:D19	D2	=COUNTIFS($A$2:$A$19,A2,$C$2:$C$19,">"&C2)+1

Filtered on the top 3 looks like:

Book1
	A	B	C	D
1	Country	City	Population	Helper
5	country01	city04	1,300,000	3
6	country01	city05	1,400,000	2
7	country01	city06	1,500,000	1
11	country02	city04	2,300,000	3
12	country02	city05	2,400,000	2
13	country02	city06	2,500,000	1
17	country03	city04	3,300,000	3
18	country03	city05	3,400,000	2
19	country03	city06	3,500,000	1
20
Sheet1

Cell Formulas
Range	Formula
D5:D7,D11:D13,D17:D19	D5	=COUNTIFS($A$2:$A$19,A5,$C$2:$C$19,">"&C5)+1

 

[robertvdb]

thanks Kevin, works fine !

 

[alansidman]

removed as wrong solution provided.

 

[alansidman]

Here is an alternative solution with Power query which is very fast especially if you have lots of records

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"Country", Order.Ascending}, {"Population", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Country"}, {{"Count", each _, type table [Country=text, City=text, Population=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Top3", each Table.LastN([Count],3)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Top3"}),
    #"Expanded Top3" = Table.ExpandTableColumn(#"Removed Other Columns", "Top3", {"Country", "City", "Population"}, {"Country", "City", "Population"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Top3",{{"Country", Order.Ascending}})
in
    #"Sorted Rows1"

 

[kevin9999]

thanks Kevin, works fine !

Happy to help Robert, and thanks for the feedback

 

[robertvdb]

Here is an alternative solution with Power query which is very fast especially if you have lots of records

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"Country", Order.Ascending}, {"Population", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Country"}, {{"Count", each _, type table [Country=text, City=text, Population=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Top3", each Table.LastN([Count],3)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Top3"}),
    #"Expanded Top3" = Table.ExpandTableColumn(#"Removed Other Columns", "Top3", {"Country", "City", "Population"}, {"Country", "City", "Population"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Top3",{{"Country", Order.Ascending}})
in
    #"Sorted Rows1"

Thanls Alan. But I have no experience at all in Power Queries.

 

[alansidman]

This should help you get started.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[Holger]

You could use the filter function, after inserting the helper column:

Book1
	A	B	C	D
1	Country	City	Population	Helper
2	country01	city01	1000000	6
3	country01	city02	1100000	5
4	country01	city03	1200000	4
5	country01	city04	1300000	3
6	country01	city05	1400000	2
7	country01	city06	1500000	1
8	country02	city01	2000000	6
9	country02	city02	2100000	5
10	country02	city03	2200000	4
11	country02	city04	2300000	3
12	country02	city05	2400000	2
13	country02	city06	2500000	1
14	country03	city01	3000000	6
15	country03	city02	3100000	5
16	country03	city03	3200000	4
17	country03	city04	3300000	3
18	country03	city05	3400000	2
19	country03	city06	3500000	1
20
21	country01	city04	1300000	3
22	country01	city05	1400000	2
23	country01	city06	1500000	1
24	country02	city04	2300000	3
25	country02	city05	2400000	2
26	country02	city06	2500000	1
27	country03	city04	3300000	3
28	country03	city05	3400000	2
29	country03	city06	3500000	1
Sheet1

Cell Formulas
Range	Formula
D2:D19	D2	=COUNTIFS($A$2:$A$19,A2,$C$2:$C$19,">"&C2)+1
A21:D29	A21	=FILTER($A$2:$D$19,D2:D19<=3)
Dynamic array formulas.

 

[kevin9999]

You could use the filter function, after inserting the helper column:

Not with 2016 (see OP's version)