Return row of max value of a category

[Lukini]

Hello All!

Here is the problem:

I want to output for each different customer, the MAX amount they bought and which item it was.

Customer Item Amount
AAA---------A-----20
AAA---------B-----31
BBB---------C-----10
CCC---------D-----22
DDD---------E-----66
DDD---------F-----44
DDD---------G-----97
BBB---------H-----16

ie.

Customer Item Amount
AAA---------B-----31
BBB---------H-----16
CCC---------D-----22
DDD---------G-----97

Thanks for your help!!!
-Luis

[tom1977]

Array formulas try attachment

[beaunydal]

If you sort by Customer ascending then by Amount descending VLOOKUP returns required values.

Beau Nydal

[Lukini]

Thanks for your reply!
Thou It can be quite complicated when you have thousands "customer" values to sort. Do you know a more robust method?

Thanks again!!
-Luis

[Fotis1991]

Hi

For the maximum quantities per customer, I think definitely give you a solution.

But to what Item is what gives this result, the solution you propose is similar to other members and requires the numbers are unique per customer.

Take a look to the attached.....

[Lukini]

Thanks for your help!

I have checked the methods and as i can see Fotis1991 method is more robust and fool proof than beaunydal method. But beaunydal is super simple and clever, thats the one im using :D

Thanks!!!
-Luis

[tom1977]

I am not sure that formula of beaunday is working (try to change cell C3 e.g to 50 and look at the result of formula)

[Lukini]

You are right, but as beaunday mentioned, the source data needs to be sorted first. if the data is updated, then the sorting needs to be done again so the output is updated. Tricky...

[tom1977]

Sorting will not help

Customer Item Amount Customer Item Max Amount
AAA B 31 AAA B 31
AAA A 55 BBB H 16
BBB H 16 CCC D 22
BBB C 10 DDD G 97
CCC D 22
DDD G 97
DDD E 66
DDD F 44

the result should be AAA A 55 (and first column is sorted)

[Lukini]

I have tried again. Took the file from beaunday and changed C3 from 20 to 55. Then selected the whole C column and sorted from Z-->A (descending) and select -expand the selection-
Then selected the whole A column and sorted A-->Z (ascending) and select -expand the selection.

This updated the output with the correct Item and max amount for each customer.

Maybe you did it in another way???