Formula to get Top 5 sales detail

[Zubair]

Dear Experts,

Please help me to get Top 5 sales with regions in D2:E6

 

[Joe4]

If we insert a column in column D to show the rank, and list out 1-5, we can get this, which I think is what you want:

Here is the formula for cell F2:

=LARGE(B$2:B$11,D2)

and the formula for cell E2:

=INDEX(A$2:A$11,MATCH(F2,B$2:B$11,0))

Then, just copy E2:F2 all the way down to row 6.

Note that think could get a little hairy if the Sales values are not unique, i.e. you have two or more records with the exact same sales.

 

[alansidman]

An alternative is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Sales", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] <= 5),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

 

[Peter_SSs]

Note that think could get a little hairy if the Sales values are not unique,

Not just hairy - it wouldn't work.

@Zubair
What do you want to happen if there is more than one region with 5th highest sales as withy my sample below?
I have included two sets of results. The first lists all that are 5th or higher, including ties. The second stops after 5 even if there are others equal.

25 01 23.xlsm
	A	B	C	D	E	F	G	H
1	Region	Sales		Region	Sales		Region	Sales
2	A	230		D	987		D	987
3	B	456		E	888		E	888
4	C	770		F	888		F	888
5	D	987		C	770		C	770
6	E	888		B	456		B	456
7	F	888		H	456
8	G	234		J	456
9	H	456
10	I	347
11	J	456
Top 5

Cell Formulas
Range	Formula
D2:D11	D2	=IF(E2="","",INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$11)/(B$2:B$11=E2),COUNTIF(E$2:E2,E2))))
E2:E11	E2	=IF(LARGE(B$2:B$11,ROWS(E$2:E2))<LARGE(B$2:B$11,5),"",LARGE(B$2:B$11,ROWS(E$2:E2)))
G2:G6	G2	=INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$11)/(B$2:B$11=E2),COUNTIF(E$2:E2,E2)))
H2:H6	H2	=LARGE(B$2:B$11,ROWS(H$2:H2))

 

[Zubair]

Many thanks all
Yes I was thinking the same if sales figures are duplicate, thanks again for the solution.

 

[Peter_SSs]

Yes I was thinking the same if sales figures are duplicate,

Even if it is not very likely with your data, it makes sense to allow for the possibility.

 

[helixmule]

A couple of other ways to achieve this. These could probably be simplified / improved.

Top 5

=VSTACK({"Rank","Region","Sales"},HSTACK(SEQUENCE(5,1,1,1),TAKE(SORTBY(A2:B11,B2:B11,-1),5,2)))

All records with a Top 5 score, ranked sequentially

=LET(filteredtable,FILTER(A2:B11,B2:B11>INDEX(SORT(UNIQUE(B2:B11),,-1),5,1)),sorted,SORTBY(filteredtable,CHOOSECOLS(filteredtable,2),-1),hstacked,HSTACK(SEQUENCE(COUNT(sorted),1,1),sorted),VSTACK({"Rank","Region","Sales"},hstacked))

All records with a Top 5 score, ranked by score (i.e. two records can have the same rank)

=LET(filteredtable,FILTER(A2:B11,B2:B11>INDEX(SORT(UNIQUE(B2:B11),,-1),5,1)),sorted,SORTBY(filteredtable,CHOOSECOLS(filteredtable,2),-1),ranks,BYROW(sorted,LAMBDA(row,MATCH(INDEX(row,1,2),SORT(UNIQUE(B2:B11),,-1),0))),VSTACK({"Rank","Region","Sales"},HSTACK(ranks,sorted)))

 

[Fluff]

As the OP's profile shows they are using 2016, none of those formulae will work for them.

 

[Arts]

Question for learning purposes
Using the data in post #4 (Peters response)

When this formula is applied =VSTACK({"Rank","Region","Sales"},HSTACK(SEQUENCE(5,1,1,1),TAKE(SORTBY(A2:B11,B2:B11,-1),5,2))) it gives the below

Rank	Region	Sales
1	D	987
2	E	888
3	F	888
4	C	770
5	B	456

What my question is when I use the formula =LARGE(TAKE($B$2:$B$11,5),ROWS($I$15:I15)) I get the below, why does it ignore the second 888 what is the TAKE in first formula doing to include both 888's but my TAKE formula excludes one of the 888's

987

888

770

456

230

Many thanks as always

 

[Fluff]

You are taking the 1st 5 rows of the data & then sorting it, you need to sort the data & then take the 1st 5 rows.