How to sort automatically by using functions? 5

[SacreBleu]

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.

 

[analogkid2digitalman]

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.

 

[SacreBleu]

analog, other readers...
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.

 

[electricpete]

Using your idea of rank
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.

 

[electricpete]

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.

 

[SacreBleu]

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.

 

[Denial]

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.

 

[SacreBleu]

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.

 

[electricpete]

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

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.

 

[electricpete]

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.

 

[electricpete]

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.

 

[GregLocock]

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.

 

[electricpete]

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.

 

[Structr]

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.

 

[Structr]

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?

 

[electricpete]

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.

 

[SacreBleu]

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.

 

[SacreBleu]

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

 

[SacreBleu]

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.