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

✔ 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.5Now 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

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 theColumn Data Formatto TEXT

do not leave it as General.

Leave the other columns alone, Click FinishYou 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 OKNow, a small

Sort Warningpop-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

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.5Select your new range of data: A1 thru B5

On the Ribbon, Click Sort

In the Sort By box, select Column B

Click OKThe

Sort Warningpop-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

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 B5On 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 OKYour 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

message edited by mmcconaghy

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?

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

message edited by mmcconaghy

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

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.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History