Auto sorting data in Excel

[echomakr]

I have a spreadsheet that is tied to real time prices that are constantly changing. Is there a way to make the spreadsheet sort real time based on the changing prices column?

Or, is there a way to sort the prices with a macro button?

Thanks for any help.

 

[circledchicken]

Hi,

This example might help. Set up a spreadsheet as follows:

• In cells A2 to A11 enter the stock names/symbols.
• In cells B2 to B11 enter sample prices (these would get updated in real time). You could enter =RAND() in this column to simulate prices for testing
• In cells C2 to C11 enter another characteristic e.g. P/E ratio
• In cell D2 enter the formula =RANK($B2,$B$2:$B$11)+COUNTIF($B$2:$B2,$B2)-1 - this assigns ranks the prices (without repeats)
• In cell E2 enter the formula =INDEX($A$2:$C$11,MATCH(SMALL($D$2:$D$11,ROWS($D$2:$D2)),$D$2:$D$11,0),COLUMNS($O$14:O$14)) and drag this across to G2 and then down to G11
• Column E should now have a list of stocks sorted by price (highest first), column F should have the associated price and column G should have the associated P/E ratio

The following resources are very useful:
http://www.mrexcel.com/td0067.html - ranking without repeats
http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/ - sorting text (this isn't used in the example but could be incorporated depending on your needs)