0

My excel sheet has this data as an example:

type   |make    |price
--------------------
cars   |honda   |500
cars   |acura   |600
cars   |ford    |550
trucks |toyota  |350
trucks |ford    |475
trucks |chevy   |400

I can use a pivot table to group by type and make, and get max of the price, but what I really want is to get the max of the price, and then the single record make associated with that max value, so that the data will ultimately look like:

type   |make    |max of price
--------------------
cars   |acura   |600
trucks |ford    |475

I can easily do this query in sql against a database, but am not finding the correct way to do this in excel.

2

Use FILTER to get the right rows:

=FILTER($A$2:$C$7,MAXIFS($C$2:$C$7,$A$2:$A$7,$A$2:$A$7)=$C$2:$C$7)

The first parameter is the data range you want to filter. The second parameter is the condition. So, max price by type is equal to the current row price.

enter image description here

1

You can do this with Power Query.

  • Group on the type without aggregating.
  • Then add two custom columns to return the make and price associated with the max price for each resultant table (group).

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="vehicles"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"type"}, {{"Grouped", each _, type table [type=nullable text, make=nullable text, price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "make", each Record.Field(Table.Max([Grouped],"price"),"make")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "price", each Record.Field(Table.Max([Grouped],"price"),"price")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"make", type text}, {"price", Currency.Type}, {"type", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Grouped"})
in
    #"Removed Columns"

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.