Extract names of top two products based on their values and criteria in other columns

[smide]

Hello.

In column A (A2:A600) I have a list of cities and towns, in column B (B2:B600) product names and in column C (C2:C600) the price of each product.
The same city can appear more than once in column A and the same product can appear more than once in column B.

In first row from column D (cells D1, E1, F1...) I have all possible cities.
I need a top two product names from each city based on their price and result(s) should be in rows 2 and 3 below the name of corresponding city.

example.

	A​	B​	C​	D​	E​	F​
1	City​	Product​	Price​	Munchen	Brisbane	Oslo
2	Munchen	cabbage	12​	orange	apple	grapes
3	Brisbane	orange	30​	cabbage	orange	fish
4	Oslo	fish	44​
5	Brisbane	tomato	19​
6	Brisbane	apple	37​
7	Munchen	orange	35​
8	Oslo	plum	28​
9	Munchen	potato	6​
10	Oslo	grapes	52​

explanation. Two most expensive products in Munchen are orange 35 (cell C7) and cabbage 12 (cell C2) so results are in cells D2 and D3 (below München in D1). The same for other cities.

* I'm using excel 2007.

 

[Sahak]

Hi Smide,

Press Ctrl+Shift+Enter to enter array formula in cell D2
=INDEX($B$2:$C10,MATCH(MAX(IF($A$2:$A10=D$1, $C$2:$C10)),$C$2:$C10,0),1)

Press Ctrl+Shift+Enter to enter array formula in cell D3
=INDEX($B$2:$C10,MATCH(LARGE(IF($A$2:$A10=D$1,$C$2:$C10),2),$C$2:$C10,0),1)

Copy D2:D3 to E2:F3

 

[alansidman]

An alternative but seemingly long means to the solution is to employ Power Query. An advantage is that any data added to the original table is automatically evaluated and the out put is updated when the Refresh All is selected

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Product", type text}, {"Price", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"City"}, {{"Data", each _, type table [City=nullable text, Product=nullable text, Price=nullable text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Product", "Price"}, {"Data.Product", "Data.Price"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Data",{{"Data.Price", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Index1"}}),
    #"Grouped Rows1" = Table.Group(#"Renamed Columns", {"City"}, {{"Data", each _, type table [City=nullable text, Data.Product=nullable text, Data.Price=nullable text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Data.Product", "Index"}, {"Data.Product", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Index] <> 3)),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[City]), "City", "Data.Product"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

Book4
	A	B	C
1	Oslo	Brisbane	Munchen
2	grapes	apple	orange
3	fish	orange	cabbage
Sheet2