numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ceemo - 13 Sep 2005 20:43 GMT

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

Reply to this Message

swatsp0p - 13 Sep 2005 20:58 GMT

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

--
swatsp0

Reply to this Message

Bill Kuunders - 13 Sep 2005 21:55 GMT

or select (highlight) the numbers and

go to

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

Signature

Greetings from New Zealand
Bill K

>
> In B1 enter this formula and then copy down your entire range:
[quoted text clipped - 15 lines]
>
> Bruce

Reply to this Message

ceemo - 14 Sep 2005 10:11 GMT

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

Reply to this Message

swatsp0p - 14 Sep 2005 14:00 GMT

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

--
swatsp0

Reply to this Message