INDEX data set, but results are sorted low to high

[freeb1893]

So I have a list of 50 records, some in the list are blank values, some in the list are numbers. There are no duplicate numbers in the list. Sometimes the list might be numbers 1 through 14 with 36 blanks, sometimes the list might be 1 through say 26 with 24 blanks. The numbers randomly located throughout the list of 50.

I want a formula to index this list of 50 and pull the results in order, so my 1st result cell is 1, 2nd result cell is 2, 3rd result cell is 3, etc. down to the largest number found in the array, ignoring blank.

Anyone know how to write this into a formula? Thanks in advance

 

[Eric W]

If your list is A1:A50, and your first result cell is B2 (header in B1), put this formula in B2 and drag down:

=SMALL($A$1:$A$50,ROWS($B$2:$B2))


Edit: You may want to put an IFERROR around it to avoid errors at the end of your list.

=IFERROR(SMALL($A$1:$A$50,ROWS($B$2:$B2)),"")