im looking a formula

i have in column a, a list of numbers goig down, none are repeated but
there are blanks in between some cells. i would like to list these
number in column b in ascending order with no spaces.

can you help ?


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=467250

In B1 enter this formula and then copy down your entire range:
=SMALL($A$1:$A$100,ROW())

Then:
B1=SMALL($A$1:$A$100,ROW()) will return the smallest number in the
range of A:A
B2=SMALL($A$1:$A$100,ROW()) will return the 2nd smallest number in A:A
B3=SMALL($A$1:$A$100,ROW()) will return the 3rd smallest number in A:A
... etc.

If your list does not begin in row 1, subtract the beginning row less
one from the row() as such: data begins in row 10:

B10=SMALL($A$9:$A$109,ROW()-9) and copy this down your range

Good Luck

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=467250

or select (highlight) the numbers and

go to

<data<sort<sort by <column a<


--
Greetings from New Zealand
Bill K
"swatsp0p" wrote in
message ...

In B1 enter this formula and then copy down your entire range:
=SMALL($A$1:$A$100,ROW())

Then:
B1=SMALL($A$1:$A$100,ROW()) will return the smallest number in the
range of A:A
B2=SMALL($A$1:$A$100,ROW()) will return the 2nd smallest number in A:A
B3=SMALL($A$1:$A$100,ROW()) will return the 3rd smallest number in A:A
.. etc.

If your list does not begin in row 1, subtract the beginning row less
one from the row() as such: data begins in row 10:

B10=SMALL($A$9:$A$109,ROW()-9) and copy this down your range

Good Luck

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile:
http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=467250

i have the following which does what i want but when it runs out of
numbers it displays an error can i get rid og this?


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=467250

delete the formula in the cells with the error message.

Let's say you have the numbers 1-75 in random order in cells A1:A100
with the remaining cells in this range blank. In column B you only
need to copy the formula down the range of B1:B75 (as you are only
going to return 75 numbers).

However, if you don't know exactly how many numbers are in the range,
you simply copy the formula down the entire range in B and then delete
those that return the error message.

HTH

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=467250