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


See More: Sort algorithm in Excel

Report •


✔ Best Answer
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. :-)

MIKE

http://www.skeptic.com/



#1
August 31, 2013 at 08:24:31
The simplest way I can think is to do a Text To Columns

Select your range of data: A1 thru A5
On the Ribbon
Select Data Tab
Select Text to Columns
Check the Delimited Button ( if it is not already )
Click Next
Select Space as your Delimiter
Click Next
Column A should be Highlighted,
Change the Column Data Format to TEXT
do not leave it as General.
Leave the other columns alone, Click Finish

You should see the upper left corner of your cells in column A
turn green, indicating an Error. Ignore it, will fix it in a minute.

Next, Select all your data, A1 thur C5
On the Ribbon, Click Sort
In the Sort By box, select Column A
Click OK

Now, 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 OK

Your data should now be in the desired order.

Now we just put the pieces back together.

In cell D1 enter the formula:

=A1&" "&B1&" "&C1

Drag down 5 rows.

Select your new sorted data, and do a Copy / Paste / Values

Delete columns A & B & C

MIKE

http://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 rows

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

Select your new range of data: A1 thru B5

On the Ribbon, Click Sort
In the Sort By box, select Column B
Click OK

The Sort Warning pop-up should appear.
Make sure the "Sort anything that looks like a number, as a number" button is selected.

Click OK

Your data should now be in the desired order.

Delete column B

See how that works.

MIKE

http://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 rows

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

Select your new range of data: A1 thru B5

On the Ribbon, Click Sort
In the Sort By box, select Column B
Make sure the Sort Order box is set at Smallest to Largest
Click OK

Your data should now be in the desired order.

Delete column B

NOTE:
Just on the off chance you have negative numbers in column A
modified the formula.

MIKE

http://www.skeptic.com/

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

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
September 1, 2013 at 09:51:02
✔ 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/


Report •

#7
September 3, 2013 at 19:04:12
Since I'm taking a while to respond I just thought I'd give you an update.

Firstly, thanks so much for your reply. The formulas seem to be working for my application. I'm still running some tests and tweaking a few things, so I'll get back to you with a detailed response once I'm done.

Second, in answer to your question about why I cannot use the wizard, you're right that it would be easier for the simplified example I've given here, but in my worksheet the list of data is dynamic, and is updated based on formulas, and will therefore change extremely frequently. The sort wizard isn't particularly useful for sorting values produced by formulas, and even if we got around that by using Paste Special -> Values, and sorting the resulting column, this would have to occur every single time a variable was changed, which is impractical, even if a macro is used, and it therefore makes sense to have the sorted list automatically updated whenever the unsorted list is.

Again, thanks for your help.


Report •


Ask Question