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

cyberdimitri

New Member
Joined
Nov 4, 2012
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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.
1670326150963.png
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
How is "SORT(CHOOSECOLS(A2:D11,1,4),2,-1)" simpler than "first sort, then remove columns not named ITEM and SOLD"?
Because formulae are simpler & update when the data changes without needing to do anything.
Also there is no need to remove columns.
 
Upvote 0
How about
Excel Formula:
=SORT(CHOOSECOLS(A2:D11,1,4),2,-1)


But no need, a simple formula can do it far simpler. ;)
Yes, this works perfectly! Thanks! Never seen the CHOOSECOLS function before, I think I will impress some colleagues with it :)
 
Upvote 0
I highly recommend spending more time with Power Query, it can do amazing things for you. For some reason it is Excel's best kept secret.
Yes, I will, after playing around with it some, it doesn't look so terribly hard to learn!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Yup, you can use
Excel Formula:
=TAKE(SORT(CHOOSECOLS(A2:D11,1,4),2,-1),5)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top