+ Reply to Thread
Results 1 to 5 of 5

How to convert an array of numbers to an ascending list

  1. #1
    Registered User
    Join Date
    01-30-2015
    Location
    Washington DC
    MS-Off Ver
    2010
    Posts
    37

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,871

    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)),"")
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,957

    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. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,871

    Re: How to convert an array of numbers to an ascending list

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

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,272

    Re: How to convert an array of numbers to an ascending list

    Please try at K2

    =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)),"")
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Convert array to numbers delete commas (Easy one )
    By ricklou in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-06-2017, 09:15 AM
  2. How to convert a range of numbers to sequence of numbers in an array
    By MetisConnect in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-25-2015, 11:53 AM
  3. Creating a list of ascending numbers with a filename extension ?
    By just_a_man in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-24-2014, 03:21 AM
  4. Convert Array to 2-Column List
    By boboliverjr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2013, 06:29 PM
  5. How to convert a list into list of consecutive numbers
    By mr. grieves in forum Excel General
    Replies: 6
    Last Post: 05-15-2013, 04:26 PM
  6. sort an array ascending
    By nikko50 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2013, 03:57 PM
  7. Convert Array to list
    By cgalla in forum Excel General
    Replies: 3
    Last Post: 09-26-2008, 09:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1