numbers

[ceemo]

im looking a formula

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

can you help

 

[swatsp0p]

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 th
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 les
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

Bruc

 

[Bill Kuunders]

or select (highlight) the numbers and

go to

<>data<>sort<>sort by <>column a<>

 

[ceemo]

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

 

[swatsp0p]

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:A10
with the remaining cells in this range blank. In column B you onl
need to copy the formula down the range of B1:B75 (as you are onl
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 delet
those that return the error message.

HTH

Bruc