## How to sort automatically by using functions?

## How to sort automatically by using functions?

(OP)

Suppose I have a range of numbers A1:A100. The numbers are not static - they are generated by formula results. They can be in any numerical order, but I need to have Excel sort them automatically, in order to do a DSUM to show a finalized summary table. How is it possible, without using VBA or pivot tables? Sorting by using the sort button is not practical- I need this to be automatic. The zeroes need to be excluded, but I already know how to exclude zero values.

Example: 34,12,2,0,3,5,... needs to be sorted to yield 2,3,5,12,34,... in cells A1,A2,A3,A4,A5, etc.

Example: 34,12,2,0,3,5,... needs to be sorted to yield 2,3,5,12,34,... in cells A1,A2,A3,A4,A5, etc.

## RE: How to sort automatically by using functions?

Without VBA or using the sort function, I do not think it is possible.

Wheels within wheels / In a spiral array

A pattern so grand / And complex

Time after time / We lose sight of the way

Our causes can't see / Their effects.

## RE: How to sort automatically by using functions?

To explain further...I didn't mean DSUM to be in order. I just want a summary table labeled in ascending order. I was wondering if the above could be done using the Rank function (not yet familiar with using that function)in combination with Offset or simialr.

## RE: How to sort automatically by using functions?

Your data in col B rows 1-5

Cell A1 is =+RANK(B1,$B$1:$B$5)

Cell A2-A5 copied down from A1

Cell E1 is =+VLOOKUP(COUNT($B$1:$B$5)+1-ROW

Cell E2-E5 copied down from cell E1

E1:E5 is sorted version of B1:B5

It works as long as there are no duplicates in your data

=====================================

Eng-tips forums: The best place on the web for engineering discussions.

## RE: How to sort automatically by using functions?

E1 is +VLOOKUP(COUNT($B$1:$B$5)+1-ROW(E1),$A$1:$B$5,2,FALSE)

=====================================

Eng-tips forums: The best place on the web for engineering discussions.

## RE: How to sort automatically by using functions?

Works like a charm...except I do have duplicates, by necessity by virtue of how data is input. Is there any easy way to parse B1:B5 to eliminate the duplicates? Any duplicate could be replaced by zero, which I can then have removed automatically using a technique previously shown in this forum.

I really appreciate your time, because this issue has been a huge problem.

## RE: How to sort automatically by using functions?

Given that you are trying to do two things (sort, then remove duplicates), I would most definitely use VBA. It could kill the two birds with the one stone. This job is ideally suited to a user-defined array function, which takes your generated range as its input, and produces as output the sorted & de-duped array.

## RE: How to sort automatically by using functions?

Sorry, but I am VBA-challenged. I am in a production environment at work, and just don't have the time to learn VBA, however simple it may be.

It would be fine if duplicates were first removed from the list, then the thing sorted.

This is for listing wood shearwall forces. The way the data is input, the original list is Shear Line labels, not in any particular order, and frequently containing duplicates. Each shear line is ultimately summarized to calculate the total shear force, which is linked to individual shearwall design worksheets. The list has to be parsed and sorted, i.e., SL1, SL2, etc. for the concept to work.

## RE: How to sort automatically by using functions?

C1:C5 = your input data

B1=+RANK(C1,$C$1:$C$5,1)+0.0001*ROW(B2)

A1=+RANK(B1,$B$1:$B$5,1)

E1=+RANK(B1,$B$1:$B$5,1)

Copy cells A1,B1,E1 down to the bottom row

Col E is the sorted version of row C, including duplicates.

Or do you prefer to eliminate duplicates?

=====================================

Eng-tips forums: The best place on the web for engineering discussions.

## RE: How to sort automatically by using functions?

E1=+VLOOKUP(ROW(E1),$A$1:$C$5,3,FALSE)

Eng-tips forums: The best place on the web for engineering discussions.

## RE: How to sort automatically by using functions?

B1=+RANK(C1,$C$1:$C$5,1)+0.0001*ROW(B1)

This assumes you have less than 10000 rows so the fractional part row * 0.0001 will never exceed 1 and cannot affect the ranking (can only break the ties). If you had more than 10000 rows you could change the multiplier to 0.00001

Eng-tips forums: The best place on the web for engineering discussions.

## RE: How to sort automatically by using functions?

c11=if(B11=B10,1e38,B11)

Then sort the list again, as described above. The dupes have been replaced by a huge value, and so will all sink to the bottom.

Finally do a similar trick replacing 1e38 by " " to give a nice clean looking list

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

## RE: How to sort automatically by using functions?

Add a row at the top for column headings. Adjust cell E2 as follows so that the lookup starts at 1 (vs)

=+VLOOKUP(ROW(E2)-1,$A$2:$C$11,3,FALSE)

Copy cells from 2nd row down.

The complete resulting excel spreadhseet shown here

http:

Eng-tips forums: The best place on the web for engineering discussions.

## RE: How to sort automatically by using functions?

h

IMPORTANT: Read the comments at the bottom of the web page - it has corrections to errors submitted by a reader.

Instead of the names Mike, Donna, David, etc., replace with SL34, SL12, SL2, etc.

Instead of sales totals shown in example, replace with 34, 12, 2, etc.

## RE: How to sort automatically by using functions?

You mentioned removal of zero values - I did a search of this forum and could not find any results. Could you give me some guidance?

## RE: How to sort automatically by using functions?

So if your data is in col A rows 1:10

Put this into column B and your done (B is sorted version of A)

=+SMALL($A$1:$A$10,ROW())

Eng-tips forums: The best place on the web for engineering discussions.

## RE: How to sort automatically by using functions?

Your last post (SMALL function) does the trick most efficiently - I gave you the coveted purple star.

Structr-

The zero remover algorithm was someting I learned in this forum...a link to some University professor's website. Since I work on 4 different computers, I lost the darn thing, but it is part of a spreadsheet I am working in for steel beam design.

The method of ranking as you suggested works, but it requires a lot more "manipulation" to get the end result.

## RE: How to sort automatically by using functions?

Actually, your suggestion works more efficiently than the zero remover algorithm. I am going back to revise the steel beam workbook too.

Thanks

## RE: How to sort automatically by using functions?

OK I found the "ZeroRemover" example. If you have a list of numbers in a column such as 23,4,9,0,67,0,34

It converts the list to: 23,4,9,67,34,#NA!,#NA!

The #NA! errors are hidden with conditional formatting. This didn't sort the list - the reason it worked as mentioned is because the raw data (beam weights) were sorted from lightest to heaviest to begin with. In light of electricpete's method of sorting, this all becomes unnecessary.