Sort Function

[Guest]

Hi,

Is there a sort function in excel? I want to do excatly the same stuff that
the Sort Menu command does but want a worksheet function for that.

The point is I already have Col A with categories and Col B with Numerical
Values which I want to sort by. (the result sorted goes to cold C and D
say...)

Its irritating to do a manual sort everytime some value in Col B changes!

Any ideas?

Thanks!

-Rahul

 

[Arvi Laanemets]

Hi

Excel worksheet functions by definition can't do this. In Excel, a function
can't change anything on sheet.

You have to use a macro, started by means of keyboard shortcut or button. Or
you have to write a Change event for worksheet.

 

[David McRitchie]

Hi Rahul,
You would need to use a macro not a function. Read about the difference
on Chip Pearson's -- see his topic.htm page then read about
Macros, as Opposed to Functions in
Macros and Functions
http://www.cpearson.com/excel/differen.htm

The easiest way would be to record a macro
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and do what you want to do
manually. You recorded macro should have most of the instructions that
you would need. You usually have to rework a generated macro.
http://www.mvps.org/dmcritchie/excel/sorting.htm
--

 

[Guest]

Thanks for your reply Arvi!

I think I did'nt phrase my question clearly!

I don't want the "funtion" to modify the data in situ; I understand that a
function is not allowed to do that.

But functions(eg. vlookup etc.) do process data from one range and write
other stuff to a different range.

Perhaps I could have something like that?

My idea was to set up a formula that sorts automatically the original set of
cols. to a new col. ; thus the sorting takes place automatically.

Worst case; I should be able to write a function like that right?

-Rahul

 

[Arvi Laanemets]

Hi

Let me be a little annoying again. Functions don't write any data anywhere.
And they don't sort data too. They display some information, accordinly
given parameters. When there is a function in cell, then cell contains the
function, not the value you see there. It may look like wordplay, but when
you ignore it, the result is misunderstanding.

Now about solution for you (I think I understand what is what you need). NB!
It works only, when all values in column B are unique!

Into D2 (I assume the row 1, and only row 1, contains headers - otherwise
you have to adjust the formula) enter the formula:
=IF(ISERROR(SMALL(B:B,ROW()-1)),"",SMALL(B:B,ROW()-1))
Into C2 enter the formula:
=IF(ISERROR(MATCH(D2,B:B,0)),"",INDEX(A:A,MATCH(D2,B:B,0),))
Copy both formulas down at least for same number of rows, as are filled in
A:B. Table in A:B is displayed in C in ascending values order.
To display new table in descending values order, replace SMALL function in
formulas with LARGE.


Arvi Laanemets

 

[Guest]

Thanks Arvi!

Thats what I wanted; I should be able to do it now.

-Rahul