+ Reply to Thread
Results 1 to 20 of 20

sort an array

  1. #1
    Registered User
    Join Date
    04-16-2014
    Location
    Wales,UK
    MS-Off Ver
    365
    Posts
    5

    sort an array

    hi, I have these array values in a cell, (16;23;48;5;8). I would like to return them in order in another cell like this, (5;8;16;23;48). is this possible? thank you

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,762

    Re: sort an array

    Are you still using Excel 2010?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    2,179

    Re: sort an array

    They are all in one cell ?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,762

    Re: sort an array

    That's what the OP said in the opening post.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,470

    Re: sort an array

    I'm optimistic that most anything is possible with time, effort and ingenuity. Here's how I would probably approach this:

    1) something like what you show (a;b;c;d) is not any kind of array in a spreadsheet, it is a text string. The first thing I would do would be to split that so that each value is in its own cell. A text to columns command (with ";" as the delimiter) will quickly and easily divide this text string into separate values.
    2) Then a simple sort command will easily sort the values left->right.
    3) Then a simple concatenation formula will rejoin the values into a text string like you started with. =A1&";"&B1&";"&...

    Of course, some of this becomes more complicated if you are not allowed to use the text to columns or the sort commands. If something like this won't work because you must use formulas rather than these commands to perform the task, then let us know. You might also check your profile, because it says you are still using Excel 2010, and Excel version could be important so we know what formulas are available to you (or if you are even required to use Excel at all for this -- I think this would be easier in google sheets if you are required to use formulas but not necessarily required to use Excel).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    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,754

    Re: sort an array

    One way:

    Please Login or Register  to view this content.
    How to install your new code
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
    [B][I]


    Then:
    =CellSort(A3)
    Attached Files Attached Files
    Glenn



  7. #7
    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,754

    Re: sort an array

    If you have upgraded to O365, then this will also work:

    =TEXTJOIN(";",TRUE,AGGREGATE(15,6,MID(SUBSTITUTE(";"&A4,";",REPT(" ",100)),100*(SEQUENCE(LEN(A4)-LEN(SUBSTITUTE(A4,";",""))+1)),100)+0,SEQUENCE(LEN(A4)-LEN(SUBSTITUTE(A4,";",""))+1)))

  8. #8
    Registered User
    Join Date
    04-16-2014
    Location
    Wales,UK
    MS-Off Ver
    365
    Posts
    5

    Re: sort an array

    I've got 365. thanks

  9. #9
    Registered User
    Join Date
    04-16-2014
    Location
    Wales,UK
    MS-Off Ver
    365
    Posts
    5

    Re: sort an array

    that worked perfectly thanks Glenn

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,762

    Re: sort an array

    OK - please update your forum profile.

  11. #11
    Registered User
    Join Date
    04-16-2014
    Location
    Wales,UK
    MS-Off Ver
    365
    Posts
    5

    Re: sort an array

    thanks. I did the transpose function along with data/text to columns and a sort. However, I have thousands of rows and it's a long manual task. thanks anyway

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,762

    Re: sort an array

    Whom are you addressing?

  13. #13
    Registered User
    Join Date
    04-16-2014
    Location
    Wales,UK
    MS-Off Ver
    365
    Posts
    5

    Re: sort an array

    thanks MrShorty. I did the transpose function along with data/text to columns and a sort. However, I have thousands of rows and it's a long manual task. thanks anyway

  14. #14
    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,754

    Re: sort an array

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

  15. #15
    Valued Forum Contributor wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    2007/MSO365
    Posts
    877

    Re: sort an array

    Bookmark and learn #6 Mr Glenn custom function code, provide the formula as follows

    Array formula

    HTML Code: 
    =MID(TEXT(SUM(LARGE(--TRIM(MID(SUBSTITUTE(A3,";",REPT(" ",20)),COLUMN(A:E)*20-19,20)),ROW($1:$5))*100^(ROW($1:$5)-1)),REPT("!,00",5)),2,14)

  16. #16
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: sort an array

    If you have the latest version of Excel including the LET function, try

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If you're willing to use some VBA,

    Please Login or Register  to view this content.
    Then use this along with the SORT function in formulas like

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by hrlngrv; 12-29-2020 at 01:18 AM. Reason: supplement

  17. #17
    Registered User
    Join Date
    12-04-2020
    Location
    Bangkok
    MS-Off Ver
    365
    Posts
    61

    Re: sort an array

    If cell contains "16;23;48;5;8" without parentheses (), then
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If cell constains "(16;23;48;5;8)" with parentheses (), then
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  18. #18
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,470

    Re: sort an array

    With all of the different options being offered, I guess I will offer one, too. Only mine only works in Google Sheets, but I think it is easier to understand what it is doing:

    1) A split() function to convert the text string to a horizontal array split(A1,";") [Excel does not support a split function, as far as I know].
    2) transpose() to transpose to a vertical array transpose(split(...))
    3) sort() to sort the vertical array sort(transpose(...),1,true)
    4) textjoin() to convert the resulting vertical array to a semicolon delimited text string. =textjoin(";",false,sort(...))

    It probably doesn't change anything, since it only works in Google Sheets, but it muddies the waters with yet another choice for the OP.

  19. #19
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: sort an array

    Quote Originally Posted by MrShorty View Post
    . . . Google Sheets . . .

    1) A split() function . . .
    Picky and arcane, but XLM to the rescue. If these cells were in column A, say, A3:A1002, make A3 the active cell, then define the name split referring to

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Called as =split, this returns a vertical array of strings.

    That said, should Excel have a worksheet SPLIT function? Yes. Will it any time soon? Ponder how long it's been since the first calls for regular expression functions in Excel in USENET posts in the 1990s, and still none in Excel. Maybe when our great grandchildren are nearing retirement.

    I do hope there are no forum rules against MSFT-directed snark.

    2) transpose() to transpose to a vertical array . . .
    One advantage to the XLM above.

    So many options. However, VBA user-defined functions are more maintainable than magic defined names calling XLM functions.

  20. #20
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: sort an array

    Love it!

    This is right up there with =SUMPRODUCT(1/COUNTIF(x,x)) to count distinct items in x.

+ 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. Replies: 10
    Last Post: 10-26-2020, 12:18 PM
  2. [SOLVED] Bubble sort to sort array of file names by file extension
    By ByteMarks in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2020, 01:13 PM
  3. [SOLVED] Using Array.sort()
    By Alexander_Golinsky in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-18-2014, 10:43 AM
  4. Replies: 0
    Last Post: 02-04-2013, 02:28 PM
  5. sort an array by the dates in a different array
    By dvb_24 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2012, 04:04 PM
  6. Sort an array:buble sort,
    By Gary''s Student in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2006, 10:50 AM
  7. [SOLVED] Sort an array
    By filo666 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2006, 08:10 PM

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