Sorting formula results

[rofl]

I have a column of vlookup formulas, and when a match is found the output is a corresponding number, when no match is found the box will remain blank. In the next column I would like to sort the results, I know I can manually copy the column and paste values, then sort the column, but since I have to re-import data columns I would like to automate this.


Is there a way to take the output value from a formula field and then have them sorted in the following column?

[martindwilson]

how do you want them sorted?
you could simply use =large(b1:b10,1), =large(b1:10,2)......

[rofl]

This works great, with one exception.

=large(b$1:b$5000,1)
=large(b$1:b$5000,2)
=large(b$1:b$5000,3)
=large(b$1:b$5000,x)

This column has a thousand results and it would appear that auto fill is not incrementing that count number (x). Is there a way I can fill this down and auto-increment?

-Somewhat answered my own question, I created a column E filled from 1-1000 and changed the formula to:

=large(b$1:b$5000,E1)

I can fill this down to auto increment:

=large(b$1:b$5000,E1)
=large(b$1:b$5000,E2)
=large(b$1:b$5000,E3)
=large(b$1:b$5000,E4)


Is there a better way to do this? (This works fine, I just like to know options for future development)

[martindwilson]

=large(b$1:b$5000,rows($a$1:a1))
rows($x$1:x1) =1 dragged down becomes
rows($x$1:x2) ie =2

[rofl]

Aaaah, it's counting the number of rows in the range. It took me a while, but I got it.

Thanks very much!