# How to convert an array of numbers to an ascending list

1. ## How to convert an array of numbers to an ascending list

I have a table that assigns unit numbers based on quantities per year. The result is an array of unit numbers with some blank cells. I need to automatically convert the array to a single-column list (ascending order, skipping blanks).

I thought there was a way to do this with one of the SORT functions, but I don't even see SORT as an available function on my work computer (using Excel 2016).

I've attached an example of what I'm trying to do. Thanks in advance for your help!

McD

2. ## Re: How to convert an array of numbers to an ascending list

There are shorter formulae that, superficially, look better. However, they use INDIRECT which is volatile. It calculates every time ANYTHING changes. On larger sheets this can cause severe performance issues.

=IFERROR(INDEX(\$1:\$1048576,ROUND(10^6*MOD(AGGREGATE(15,6,(ROW(\$C\$10:\$G\$13)/10^6+COLUMN(\$C\$10:\$G\$13))/(\$C\$10:\$G\$13<>""),ROWS(K\$9:K9)),1),0),INT(AGGREGATE(15,6,(COLUMN(\$C\$10:\$G\$13)+ROW(\$C\$10:\$G\$13)/10^6)/(\$C\$10:\$G\$13<>""),ROWS(K\$9:K9))/1)),"")

3. ## Re: How to convert an array of numbers to an ascending list

The values in your first table are text values, though they look like numbers. You can use this formula instead in C10:

=IF(C\$8>ROWS(\$1:1)-1,(RIGHT(C\$7,2)&TEXT(ROWS(\$1:1),"00"))*1,"")

which can be copied across to G10 and down to row 13 to give you numbers instead.

Then you can use this formula in K10:

=IFERROR(SMALL(\$C\$10:\$G\$13,ROWS(\$1:1)),"")

Copy down as required.

Hope this helps.

Pete

4. ## Re: How to convert an array of numbers to an ascending list

Hahaha. I overcomplicated it a bit (and a bit more...)

5. ## Re: How to convert an array of numbers to an ascending list

=IFERROR(AGGREGATE(15,6,MOD(\$C\$7:\$G\$7,100)*100+ROW(A\$1:A\$9)/(ROW(A\$1:A\$9)<=\$C\$8:\$G\$8),ROWS(K\$10:K10)),"")

There are currently 1 users browsing this thread. (0 members and 1 guests)