Sort excel formula output?

Question

I have made an excel column with a formula. Now I would like to sort this column by number... Just using sort won't work, because it sorts the formula ( =if(; =abs( etc.) and not the numbers that I see. Can somebody help me with this please?

Answer 1

Yes you can sort Excel/LibreOffice cells using just formulas only.

1. My unsorted numbers ( or words ) are listed horizontally. e.g. B29 – G29 ( 6 numbers ). I choose 29 so that it wont be confused with the 1 used in RANK function :D

2. My sorted numbers shall be in cells J29-O29.

3. The formula for cell J29 is

=IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1,$C29,IF(RANK($D29,$B29:$G29,1)=1,$D29,IF(RANK($E29,$B29:$G29,1)=1,$E29,IF(RANK($F29,$B29:$G29,1)=1,$F29,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29))))))

1. The formula for cell K29 is … just convert all the “=1″ into “=2″

2. The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.

3. The RANK function will rank every cell in the range. There will not be any unranked.

4. The last part .. ,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29) If there are more than one same number .. meaning there are more than one number of the same rank.. it would duplicated the previous number of the same rank.

5. To sort words, you need to first convert words into ASCII using the CODE function. The RANK function works only with numbers.