×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# How to sort automatically by using functions?5

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

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

I looked at DSUM in help, there was no criteria shown or discussed that the data had to be in any order.

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?

(OP)
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?

3
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?

Oops. Cell E1 was truncated. Should have been
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?

(OP)
electricpete,
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?

You have stated that you do not want to use VBA.  Your third post implies that you want to remove duplicated entries from your sorted list before using it for whatever your ultimate purpose is.

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?

(OP)
Denial,
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?

OK, Let's say you don't mind the duplicates showing up in your list.

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?

Correction, E1 should have been
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?

Although it won't affect the function, it would be a little cleaner to define B1 as
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?

Once you've got a sorted list, with dupes, then generate a new list

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?

I made one more change for appearance.

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://home.houston.rr.com/electricpete/eng-tips/sb.xls

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

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

Here is another possibility:

http://www.exceltip.com/st/Ranking_Salespeople_According_To_Sales_Figure/846.html

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?

SacreBleu,
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?

Large (and it's partner small) seems to be a more straightforward solution and don't have a problem with duplicates (well, duplicates are listed, same as my solution above).

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?

(OP)
Electricpete,
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?

(OP)
Greg,
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?

(OP)
Structr,
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.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!