+ Reply to Thread
Results 1 to 10 of 10

re-ordering list in a single cell

  1. #1
    Registered User
    Join Date
    07-21-2013
    Location
    london england
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    57

    re-ordering list in a single cell

    i have a list inside a single cell but the list is not in any order. The list in the cell is 5th, 3rd, 1st, 4th, 2nd
    How can I re-order these into the correct order in the next cell? ie.1st, 2nd, 3rd, 4th, 5th
    Just assume the list is in cell A2, so I will require the re-ordered list in cell A3.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,769

    Re: re-ordering list in a single cell

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    07-21-2013
    Location
    london england
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    57

    Re: re-ordering list in a single cell

    example attached.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    907

    Re: re-ordering list in a single cell

    Is what you posted truly representative of your actual data? If not, please give us a file with real type values in it AND show us what you expect for the "correct order".

    The following general note will give you an idea why I am asking the above question...
    For questions that you ask on this (or any) forum, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data).

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,825

    Re: re-ordering list in a single cell

    Hi fumusic,

    Find the attached with a User Defined Function called SortCell(). See the example in A2 where it sorted comma separated stuff in cell A1.
    UDF SortCell comma seperated.xlsm
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  6. #6
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    907

    Re: re-ordering list in a single cell

    More than likely I was overthinking this question too much.

    @MarvinP,
    Assuming you are right as to what the OP wants, here is another way to write your SortCell function...
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 03-25-2020 at 02:55 AM.

  7. #7
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,941

    Re: re-ordering list in a single cell

    @Rick Rothstein
    Already some time ago, I've noticed that there are some problems with ArrayList on some computers.
    So seeing your post I wanted to check if they persist.

    For instance on the computer, I'm using to write this post (new one, purchased Jan 2020, Win10 Home 64, installed as local program Excel for Office 365 MSO, 32 bit, compilation 16.0.12527.20260.
    The result of function is just #ARG!
    I've tried also early binding version (Added in VBE: Tools-References-mscorlib.dll found in Windows\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb) and tried:
    Please Login or Register  to view this content.
    Same result, so changed it into Sub to get a bit better error info:
    Please Login or Register  to view this content.
    And now getting at least error info. It's:
    Run-time Error '-2146232576 (80131700)' Automation Error
    Any hint(s) for me?
    Best Regards,

    Kaper

  8. #8
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    907

    Re: re-ordering list in a single cell

    I am using XL2010 and the code I posted works fine for me. It should also work fine for the OP since he appears to be using XL2010 also. It would surprise me if Microsoft changed Excel to not work with the built-in System's Collections, but who knows. I note that MarvinP is using XL365 and XL2016, so if he revisits this thread, perhaps he would be kind enough to try my code on his two versions of Excel and report back how it works for him on them.

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,825

    Re: re-ordering list in a single cell

    Hi Rick and Kaper,

    Rick's code seems to work with my machine version. I wonder if it could be a Reference Library that we got in the USA and Kaper has different in Poland? Under my VBA > Tools > Available References: I have only 4 checked.
    1. VBA
    2. Microsoft Excel 16.0 Object Library
    3. OLE Automation
    4. Microsoft Office 16.0 Object Library
    If Kaper doesn't have one of the above checked, he might try Rick's code again with the included Reference/DLL.

  10. #10
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,941

    Re: re-ordering list in a single cell

    No, it's not it. These four I do have checked.
    I'd suspect rather that older versions of .NET are not installed on my computer. As I said - I was using ArrayList some time ago on other computers. I first spotted it four years ago on the forum on this thread: https://www.excelforum.com/excel-for...ml#post4341369 (still have it in my subscriptions, so it was easy to find).
    And it worked on my home computer.
    Then used it again after some time (I needed sorting in a directory). While it worked fine at home, it did not in the office, so - as i needed it the same day I rewrote the sub to use quicksort before outputting data and gave up with ArrayList.
    Now spotted it again, and tried to check it again - and may be add to my "basic set" - but zonk: it didn't work

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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