May 02 2021 01:29 AM
hi,
CUSTOMER CODE | SALES | SALES ADVISOR |
123456 | 3400000 | CARLOTTA |
123456 | 300000 | MERCO |
123456 | 60000 | CATERINA |
123456 | 60000 | CATERINA |
123456 | 23500 | MATTEO |
123456 | 23500 | MATTEO |
987654 | 3436 | CATERINA |
987654 | 223344 | MERCO |
987654 | 4567 | CARLOTTA |
987654 | 3456 | MATTEO |
hi everyone,
Given the 'CUSTOMER CODE' with multiple 'SALES' I would like to return the 'SALES ADVISOR' with the best/max 'SALES' value.
i.e. given '987654' -----> 'MERCO'
I tried different vlookup with no success.
Thanks in advance
Matteo
May 02 2021 02:50 AM
SolutionWith the customer code in E2, the maximum sales amount in F1 is given by
=MAXIFS($B$2:$B$11,$A$2:$A$11,E2)
and the corresponding sales advisor in G1 is given by
=INDEX($C$2:$C$11,MATCH(1,INDEX(($A$2:$A$11=E2)*($B$2:$B$11=F2),,),0))
These formulas can be filled down, if required.
May 02 2021 02:54 AM
May 02 2021 02:59 AM
May 02 2021 03:33 AM
@MacTeo84 And one more. This time with Power Query, as long as you are not on a Mac.
Add sales data to the blue table and refresh the green one to update the summary.
File attached.
May 02 2021 03:37 AM
May 02 2021 03:48 AM - edited May 02 2021 03:49 AM
@MacTeo84 Actually, you can add data to the blue table and refresh the green one in this file on a Mac, but you can't edit the queries. You can't even look at them, which makes it quite useless.
May 02 2021 08:27 AM
As variant
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source, {"CUSTOMER CODE"},
{{"Count", each Table.Sort(_,{{"SALES", Order.Descending}}){0} }}
),
#"Expanded Count" = Table.ExpandRecordColumn(
#"Grouped Rows",
"Count",
{"SALES", "SALES ADVISOR"}, {"SALES", "SALES ADVISOR"}
)
in
#"Expanded Count"
May 02 2021 02:50 AM
SolutionWith the customer code in E2, the maximum sales amount in F1 is given by
=MAXIFS($B$2:$B$11,$A$2:$A$11,E2)
and the corresponding sales advisor in G1 is given by
=INDEX($C$2:$C$11,MATCH(1,INDEX(($A$2:$A$11=E2)*($B$2:$B$11=F2),,),0))
These formulas can be filled down, if required.