Home/ Questions/Q 15588
Next
Answered
computing
  • 0
Asked: 2021-12-19T14:44:56+00:00 In:

Solved Sort Algorithm In Excel

  • 0

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 K
3.0 K
10.0 K
3.0 K, FWHM=0.4
1.5 K

After sorting it needs to look like this:
1.0 K
1.5 K
3.0 K
3.0 K, FWHM=0.4 (if this is swapped with the one above it doesn’t matter)
10.0 K

An 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.

Share

    1 Answer

    1. Best Answer
      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. 🙂

      MIKE

      http://www.skeptic.com/

      • 0