sorting with duplicates and zero values

[Tkeller]

I have an array that will have 10 numbers in it. Within these numbers, there could be zero values as well as duplicate values. For example:

1) 0
2) 30
3) 50
4) 0
5) 20
6) 30
7) 20
8) 0
9)100
10) 90

I need to be able to sort these using a (array) formula in ascending order, including duplicate and zero values. For example, from above, the order would be:

1,4,8,5,7,2,6,3,10,9

I know about "rank", but this does not help me with the duplicates. Any help would be appreciated.

Thanks!

 

[HappyMeal]

Hi Tkeller,

Would you be willing to add some columns? you could just do a =Left(a2,find etc..) formula and do a simple sort?

Have '1) 0' as two hidden columns '1' & '0' then sort by '0' then '1'.

I don't think it needs to be complicated.

Hope this helps,
HappyMeal

 

[Tkeller]

Happymeal.

Thanks. I think I could add some columns if it helped me to solve this. Please help my out though, as I am not sure I understand the solution. This would have to be a formula-driven automated solution - note that I am just giving an example above, the actual values will be different with each run of the model.

 

[HappyMeal]

If '1) 0' was in cell A2 you would use the following formulas

=LEFT(A2,FIND(")",A2&")")-1)

=RIGHT(A2,LEN(A2)-FIND(")",A2))

Then use your sort on the two columns with the formulas, this will sort the data the way you want it.

Hope this helps,
HappyMeal

 

[RatExcel]

Assuming your data starts in A1 enter below formula in B1 and drag it down:

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

Sample workbook:

https://app.box.com/s/bhbkgw71aj8qkv54zzqo

 

[Tkeller]

Thanks, I will try this.