Auto Sort Excel 2016

[Rubber Beaked Woodpecker]

In A1:A10 I have the product types.
In B1:B10 I have the number of products sold.

These cells are auto populated using an array formula. However these cells can be out of order and I would like to have the highest number of sold products in A1:B1. I know I can use the sort function but is there a way to do this using a lookup table as I would rather not use the manual sort function. I use excel 2016.

Thank you

 

[sandy666]

use Power Query, sort values descending, then load to the sheet
after update source table and refresh query table it will be sorted with your definition

 

[Fluff]

I use excel 2016.

Can you please update your account details to show this. Thanks

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E
1	SK17 8DG	48		SK17 1AQ	86
2	BD13 2DD	52		HX4 9LF	83
3	SK17 9PH	74		SK17 9PH	74
4	SK17 7NP	51		BD13 1EB	74
5	BD13 1EB	74		OL1 4SQ	69
6	SK17 9DZ	26		BD13 2DD	52
7	SK17 0JP	32		SK17 7NP	51
8	SK17 1AQ	86		SK17 8DG	48
9	HX4 9LF	83		SK17 0JP	32
10	OL1 4SQ	69		SK17 9DZ	26
Main

Cell Formulas
Range	Formula
D1:D10	D1	=INDEX($A$1:$A$10,AGGREGATE(15,6,(ROW($A$1:$A$10)-ROW($A$1)+1)/($B$1:$B$10=E1),COUNTIFS(E$1:E1,E1)))
E1:E10	E1	=LARGE($B$1:$B$10,ROWS(E$1:E1))

 

[sandy666]

before source result Product Sold Product Sold item01 3 item02 42 item02 42 item06 42 item03 7 item05 22 item04 18 item04 18 item05 22 item03 7 item06 42 item01 3

after update and refresh source result Product Sold Product Sold item01 3 item09 102 item02 42 item08 82 item03 7 item07 62 item04 18 item02 42 item05 22 item06 42 item06 42 item05 22 item07 62 item04 18 item08 82 item03 7 item09 102 item01 3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Sort = Table.Sort(Source,{{"Sold", Order.Descending}, {"Product", Order.Ascending}})
in
    Sort

 

[Rubber Beaked Woodpecker]

How about

+Fluff New.xlsm
	A	B	C	D	E
1	SK17 8DG	48		SK17 1AQ	86
2	BD13 2DD	52		HX4 9LF	83
3	SK17 9PH	74		SK17 9PH	74
4	SK17 7NP	51		BD13 1EB	74
5	BD13 1EB	74		OL1 4SQ	69
6	SK17 9DZ	26		BD13 2DD	52
7	SK17 0JP	32		SK17 7NP	51
8	SK17 1AQ	86		SK17 8DG	48
9	HX4 9LF	83		SK17 0JP	32
10	OL1 4SQ	69		SK17 9DZ	26
Main

Cell Formulas
Range	Formula
D1:D10	D1	=INDEX($A$1:$A$10,AGGREGATE(15,6,(ROW($A$1:$A$10)-ROW($A$1)+1)/($B$1:$B$10=E1),COUNTIFS(E$1:E1,E1)))
E1:E10	E1	=LARGE($B$1:$B$10,ROWS(E$1:E1))

Thank you Fluff that works perfect!

 

[Fluff]

Glad to help & thanks for the feedback.