Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

I found out that the formula you gave to Raj is not working. I think it is due to the "ROW(1:1)" part of it which means nothing. If you want to have a valid rank for the SMALL(Array;rank) you should declare ROW(INDIRECT("1:"&ROWS(List)) as a vector of ranks.

I am happy you got the formula working.
The formula is an array formula. To enter the formula as an array formula, type the formula in a cell and then press and hold CTRL + SHIFT and then press ENTER once.

Thanks for formula however if the list has blanks it puts them up top and the data is in the bottom. Since I am using dynamic validation how can i put them up top so the user doesn't have to scroll down for selection?

Oscar, how would i change your formula if i want to work with COLUMNS rather than ROW... e.g. I have a single row of data in 5 columns, CDAEB and i want formulas in the next 5 columns that results in ABCDE.

I tried swapping the match reference and row(1:1) to column(1:1) but it doesnt work... many thanks.

what is the purpose of [COUNTIF(List, "<"&List)]? I tried the same overall formula but I replaced this part with a direct reference to the named range and switched SMALL for LARGE which resulted in the formula below:

=INDEX(List, MATCH(LARGE(List, ROW(1:1)), List, 0))

This produced the same results as your original formula which is what made me wonder why the [COUNTIF(List, "<"&List)] portion needs to be there at all.

Also, in my attempt to understand what this part of the formula is doing I came across something that confused me even more. If the section in question [COUNTIF(List, "<"&List)] is put into cells by itself, without the [INDEX(List...], then the results are dependant on the row where the formula was originally inputted and where the formula was copied. I'm assuming this is because the countif outputs an array but I don't understand why. Can you explain this for me?

in the sort order column i used the array formula you gave to Raj=MATCH(SMALL(COUNTIF(List, "<"&List)+ROW(List)/1048576, ROW(1:1)), COUNTIF(List, "<"&List)+ROW(List)/1048576, 0), and the second column is the text that needs sorting in alphabetical order, in the third column i placed the formula that Chandoo used in his article =VLOOKUP(ROW()-ROW($K$1),$I$2:$J$12,2,FALSE. By doing this helped me to avoid problems with non unique values. I am wondering if u have a way for me to by bass the help column altogether?
Sort Order Info needing Sorting Sorted Info
1 WORK COMPLETE WORK COMPLETE
2 WORK COMPLETE WORK COMPLETE
3 WORK COMPLETE WORK COMPLETE
4 WORK COMPLETE WORK COMPLETE
5 WORK COMPLETE WORK COMPLETE
6 WORK COMPLETE WORK COMPLETE
7 WORK COMPLETE WORK COMPLETE
8 WORK COMPLETE WORK COMPLETE
9 WORK COMPLETE WORK COMPLETE
10 WORK COMPLETE WORK COMPLETE
11 WORK COMPLETE WORK COMPLETE

Jernej,
Can you give an excel example of the final thing that gave you your desired results.
You wanted above "i would like to get first column sorted ascending and second column belonging number to the number of the first column:

column A ; column B
2 1
2 17
2 1
2 18
3 5
3 19
7 11
7 26"

I need to do the same thing. Sort the first column and then after the sorting is done, put the associated 2nd column next to the first, as you mentioned. I have been successful in sorting the first column.
please help
thanks

I am trying to sort the first column into the second. Wherever the number is 9999 it duplicates what would be the next number. It should be shifting up and moving the 9999 to the end. I am working with data sets of about 200 numbers.

I know its possible, but I can't manage to do it :) I have in col A 200 names, but between them, there are empty cells, and they need to stay. Is it possible in the sort (col B) to sort from A-Z without the empty cells.

I have a table of example data: Column A contains Names, Columns B and C both contain Weight and Height data respectively. How can I sort Columns B and C to give an accurate combination of both Weight and Height from Largest -> Smallest.

My solution is much simpler.
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))))))
3. The formula for cell K29 is … just convert all the “=1″ into “=2″
4. The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.
5. The RANK function will rank every cell in the range. There will not be any unranked. 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 first number of the same rank.

i used this formula successfully. but i can't understand why (--(sum(a2=$a$2:a2)) has been used in this formula. and what the logic of (--(sum(a2=$a$2:a2)). I used row function in place of (--(sum(a2=$a$2:a2)), the result was not proper when the values were same.

I tried using the formula for sorting two columns using the second column, with the second column having the numbers for the ranking and the first column having the text that should be ranked along with it.

The formula worked fine for small lists, but started producing only a "1" in every cell when used with a large array (>2,000 entries).

I have 64 bit Excel and am surprised the program cannot handle the formula. In fact, using the SORT feature from the pull-down menus works just fine, so it doesn't seem to me that the program should be unable to do this. Rather, it is simply my inability to make your formula work. So do you have any suggestions?

The problem you are facing is in the "ROW" function within the formula. Since it is an array formula, it is only recognizing the first smallest number across board. Try replacing the "ROW(1:1)" with "ROW(List)-Row(1)" this is assuming your data "List" named range starts from row 2. Hope this helps.