# Solved Sort algorithm in Excel August 31, 2013 at 05:20:24
Specs: Windows 7
 I'm trying to construct some formulas in Excel 2007 to sort some data and I'm just having trouble implementing a suitable sorting algorithm that gives me the result I want.My data looks a bit like this:1.0 K3.0 K10.0 K3.0 K, FWHM=0.41.5 KAfter sorting it needs to look like this:1.0 K1.5 K3.0 K3.0 K, FWHM=0.4 (if this is swapped with the one above it doesn't matter)10.0 KAn alphabetical sort doesn't work once I have numbers above 10. I've had some success using the Left and Search functions to extract out the initial numbers, then using the Small function to give me a numerical sort of this second set, but I can't seem to get a working method to use this second set to sort the first. The 3.0 K vs 3.0 K, FWHM=0.4 data is giving me some problems in relation to this.Any idea how I can accomplish this sort? I don't mind if I have to do it via multiple steps, however I cannot use the sort wizard, only formulas, as the former is not suitable for this application in the context of the rest of my workbook.Thanks in advance for any help you can offer. See More: Sort algorithm in Excel September 1, 2013 at 09:51:02
 OK, at the bottom of the link I posted, there is a solution for a two column sort.To get it to work with your data:First use the formula:=VALUE(LEFT(A1,FIND(" ",A1,1)-1))In cell B1 and drag down, Your data should now look like:``` A B 1) 1.0 K 1.0 2) 3.0 K 3.0 3) 10.0 K 10.0 4) 3.0 K, FWHM=0.4 3.0 5) 1.5 K 1.5 ```Now in cell E1 enter the first Array Formula:=INDEX(\$B\$1:\$B\$5, MATCH(SMALL(COUNTIF(\$B\$1:\$B\$5, "<"&\$B\$1:\$B\$5), ROW(1:1)), COUNTIF(\$B\$1:\$B\$5, "<"&\$B\$1:\$B\$5), 0))In cell D1 enter the second Array Formula:=INDEX(\$A\$1:\$A\$5, SMALL(IF(E1=\$B\$1:\$B\$5, MATCH(ROW(\$B\$1:\$B\$5), ROW(\$B\$1:\$B\$5))), SUM(--(E1=\$E\$1:E1))))Don't forget it's CTRL-SHIFT-ENTER for Array formulas.Drag down both formulas and you data should be correct.The Sort Wizard would have been soooooo much easier. :-)MIKEhttp://www.skeptic.com/

#1 August 31, 2013 at 08:24:31
 The simplest way I can think is to do a Text To ColumnsSelect your range of data: A1 thru A5On the Ribbon Select Data TabSelect Text to ColumnsCheck the Delimited Button ( if it is not already )Click NextSelect Space as your DelimiterClick NextColumn A should be Highlighted, Change the Column Data Format to TEXTdo not leave it as General.Leave the other columns alone, Click FinishYou should see the upper left corner of your cells in column Aturn green, indicating an Error. Ignore it, will fix it in a minute.Next, Select all your data, A1 thur C5On the Ribbon, Click SortIn the Sort By box, select Column AClick OKNow, a small Sort Warning pop-up should appear.Make sure the "Sort anything that looks like a number, as a number"button is selected.Click OKYour data should now be in the desired order.Now we just put the pieces back together.In cell D1 enter the formula:=A1&" "&B1&" "&C1Drag down 5 rows.Select your new sorted data, and do a Copy / Paste / ValuesDelete columns A & B & CMIKEhttp://www.skeptic.com/

Report •

#2 August 31, 2013 at 10:02:19
 Here is another, somewhat simpler method:With your data in column A, cells 1 thru 5 again.In cell B1 enter the formula:=LEFT(A1,FIND(" ",A1,1)-1)Drag down 4 rowsYour data should now look like:``` A B 1) 1.0 K 1.0 2) 3.0 K 3.0 3) 10.0 K 10.0 4) 3.0 K, FWHM=0.4 3.0 5) 1.5 K 1.5 ```Select your new range of data: A1 thru B5On the Ribbon, Click SortIn the Sort By box, select Column BClick OKThe Sort Warning pop-up should appear.Make sure the "Sort anything that looks like a number, as a number" button is selected.Click OKYour data should now be in the desired order.Delete column BSee how that works.MIKEhttp://www.skeptic.com/

Report •

#3 August 31, 2013 at 10:10:10
 OK, last one, this is even simpler that the last one.With your data in column A, cells 1 thru 5 again.In cell B1 enter the formula:=ABS(LEFT(A1,FIND(" ",A1,1)-1))=VALUE(LEFT(A1,FIND(" ",A1,1)-1))Drag down 4 rowsYour data should now look like:``` A B 1) 1.0 K 1.0 2) 3.0 K 3.0 3) 10.0 K 10.0 4) 3.0 K, FWHM=0.4 3.0 5) 1.5 K 1.5 ```Select your new range of data: A1 thru B5On the Ribbon, Click SortIn the Sort By box, select Column BMake sure the Sort Order box is set at Smallest to LargestClick OKYour data should now be in the desired order.Delete column BNOTE:Just on the off chance you have negative numbers in column Amodified the formula.MIKEmessage edited by mmcconaghy

Report •

Related Solutions

#4
September 1, 2013 at 05:42:40
 Hey, thanks for your help.Unfortunately I really must use formulas, rather than the sort wizard. Do you know how I might be able to achieve what you've described using formulas instead of the sort wizard?

Report •

#5 September 1, 2013 at 09:27:28
 If I may ask, why don't you want to use the Wizard?Makes life so much easier.Here is an Array formula that should do what you want with an extensive explanation of what's going on:http://www.get-digital-help.com/200...EDIT ADDED:The above does not work.I just tried it on a copy of your data.It will sort Alphabetically, not Numerically.MIKEmessage edited by mmcconaghy

Report •

#6 September 1, 2013 at 09:51:02
 OK, at the bottom of the link I posted, there is a solution for a two column sort.To get it to work with your data:First use the formula:=VALUE(LEFT(A1,FIND(" ",A1,1)-1))In cell B1 and drag down, Your data should now look like:``` A B 1) 1.0 K 1.0 2) 3.0 K 3.0 3) 10.0 K 10.0 4) 3.0 K, FWHM=0.4 3.0 5) 1.5 K 1.5 ```Now in cell E1 enter the first Array Formula:=INDEX(\$B\$1:\$B\$5, MATCH(SMALL(COUNTIF(\$B\$1:\$B\$5, "<"&\$B\$1:\$B\$5), ROW(1:1)), COUNTIF(\$B\$1:\$B\$5, "<"&\$B\$1:\$B\$5), 0))In cell D1 enter the second Array Formula:=INDEX(\$A\$1:\$A\$5, SMALL(IF(E1=\$B\$1:\$B\$5, MATCH(ROW(\$B\$1:\$B\$5), ROW(\$B\$1:\$B\$5))), SUM(--(E1=\$E\$1:E1))))Don't forget it's CTRL-SHIFT-ENTER for Array formulas.Drag down both formulas and you data should be correct.The Sort Wizard would have been soooooo much easier. :-)MIKEhttp://www.skeptic.com/ 