How to populate and sort a table by the highest value in another table

[cyberdimitri]

I have a list of items in column A and the amount that was sold in a given period in column B.
How can I create another table, sorted by the item sold most to least?
In the example below, I want the table on D1 to have the items listed in the highest to lowest sell order.

 

[Peter_SSs]

Like this?

22 12 06.xlsm
	A	B	C	D	E
1
2	Item 1	8		Item 4	654
3	Item 2	65		Item 6	265
4	Item 3	25		Item 8	145
5	Item 4	654		Item 2	65
6	Item 5	25		Item 9	48
7	Item 6	265		Item 3	25
8	Item 7	25		Item 5	25
9	Item 8	145		Item 7	25
10	Item 9	48		Item 10	25
11	Item 10	25		Item 1	8
Sort by sold

Cell Formulas
Range	Formula
D2:E11	D2	=SORT(A2:B11,2,-1)
Dynamic array formulas.

 

[Automatrix]

Power Query solution: Import first table, sort by SOLD, load as new table.

 

[cyberdimitri]

Like this?

22 12 06.xlsm
	A	B	C	D	E
1
2	Item 1	8		Item 4	654
3	Item 2	65		Item 6	265
4	Item 3	25		Item 8	145
5	Item 4	654		Item 2	65
6	Item 5	25		Item 9	48
7	Item 6	265		Item 3	25
8	Item 7	25		Item 5	25
9	Item 8	145		Item 7	25
10	Item 9	48		Item 10	25
11	Item 10	25		Item 1	8
Sort by sold

Cell Formulas
Range	Formula
D2:E11	D2	=SORT(A2:B11,2,-1)
Dynamic array formulas.

Thanks Peter, it worked!

But what if I make it a little more difficult?
Like in the example below?

Now there are 2 extra columns which I need to be omitted from the sorted table (F1:G11), I just need the 'ITEM' and the "SOLD' columns, sorted by SOLD. Is that possible?

 

[cyberdimitri]

Power Query solution: Import first table, sort by SOLD, load as new table.

Thanks for your answer. I'm not sure how to do what you suggest, too much of a noob
EDIT: after googling "power query", I'm sure the solution is not for me, wayyy too complex.
Again, thanks for the answer!

 

[Automatrix]

Thanks for your answer. I'm not sure how to do what you suggest, too much of a noob
EDIT: after googling "power query", I'm sure the solution is not for me, wayyy too complex.
Again, thanks for the answer!

First this:

To get this:

Book2
	A	B
1	ITEM	SOLD
2	Item 1	8
3	Item 2	65
4	Item 3	25
5	Item 4	654
6	Item 5	25
7	Item 6	265
8	Item 7	25
9	Item 8	145
10	Item 9	48
11	Item 10	25
Sheet1

Then this:

Then click here:

Choose "Sort Descending".

Close & Load To...

New Table in a new or existing worksheet.

And you're done! This method allows you to do all sorts of other useful operations on your data, without writing code at all.

Book2
	A	B
1	ITEM	SOLD
2	Item 4	654
3	Item 6	265
4	Item 8	145
5	Item 2	65
6	Item 9	48
7	Item 7	25
8	Item 5	25
9	Item 3	25
10	Item 10	25
11	Item 1	8
Table1

 

[Automatrix]

Thanks Peter, it worked!

But what if I make it a little more difficult?
Like in the example below?
View attachment 80293
Now there are 2 extra columns which I need to be omitted from the sorted table (F1:G11), I just need the 'ITEM' and the "SOLD' columns, sorted by SOLD. Is that possible?

Power Query has a "Remove Columns" action for this.

 

[Fluff]

Now there are 2 extra columns which I need to be omitted from the sorted table (F1:G11), I just need the 'ITEM' and the "SOLD' columns, sorted by SOLD. Is that possible?

How about

=SORT(CHOOSECOLS(A2:D11,1,4),2,-1)

Power Query has a "Remove Columns" action for this.

But no need, a simple formula can do it far simpler.

 

[Automatrix]

How about

=SORT(CHOOSECOLS(A2:D11,1,4),2,-1)

But no need, a simple formula can do it far simpler.

How is "SORT(CHOOSECOLS(A2:D11,1,4),2,-1)" simpler than "first sort, then remove columns not named ITEM and SOLD"?

 

[cyberdimitri]

And you're done! This method allows you to do all sorts of other useful operations on your data, without writing code at all.

Wow, amazing! Requires some getting used to, but indeed it's a super helpful trick to know. Thanks!