dcsimg
Results 1 to 9 of 9

Thread: [RESOLVED] Sort comma separated numbers and normal numbers together

  1. #1

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Resolved [RESOLVED] Sort comma separated numbers and normal numbers together

    Hello experts!

    I have some data that a column contains number alone and comma separated together, I need to sort considering both criteria, example as shown below

    Col A
    3
    5
    7,8,9
    11
    6

    expecting result as below:

    Col A
    3
    5
    6
    7,8,9
    11

    What we get now is:

    Col A
    3
    5
    6
    11
    7,8,9

    Any idea / code / method would be appreciated!!

    Thanks in advance!
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,541

    Re: Sort comma separated numbers and normal numbers together

    what if you have one cell with 7,8,9 and another with 79.

    Which comes first?

  3. #3
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,192

    Re: Sort comma separated numbers and normal numbers together

    and a cell with 7

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,825

    Re: Sort comma separated numbers and normal numbers together

    how are you sorting now?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Sort comma separated numbers and normal numbers together

    Hi Vbfbryce, thanks for your reply, 79 comes first, but i need 7,8,9 to be first.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  6. #6

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Sort comma separated numbers and normal numbers together

    Quote Originally Posted by westconn1 View Post
    how are you sorting now?
    normal sorting through VBA
    Code:
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:A7")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  7. #7
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,541

    Re: Sort comma separated numbers and normal numbers together

    We really need to better understand your rule set before we can help.

    For example, do you want to use only the first digit when you have something like 7,8.9?

    To JDC's question, does 7,8,9 come after 7?

    If you have a 7,8 does it come before a 7,8.9? etc.

  8. #8

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Sort comma separated numbers and normal numbers together

    Quote Originally Posted by vbfbryce View Post
    We really need to better understand your rule set before we can help.

    For example, do you want to use only the first digit when you have something like 7,8.9?

    To JDC's question, does 7,8,9 come after 7?

    If you have a 7,8 does it come before a 7,8.9? etc.
    yes, you are right, need to use only 1st digit for sorting. The cells may contain individual numbers and comma separated numbers too.

    Thanks!
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  9. #9

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Sort comma separated numbers and normal numbers together

    Quote Originally Posted by vbfbryce View Post
    We really need to better understand your rule set before we can help.

    For example, do you want to use only the first digit when you have something like 7,8.9?

    To JDC's question, does 7,8,9 come after 7?

    If you have a 7,8 does it come before a 7,8.9? etc.
    yes, you are right, need to use only 1st digit for sorting. The cells may contain individual numbers and comma separated numbers too.

    I have solved this by looping thru rows to extract 1st chr then sort using that column.
    Thanks to all for ur support.
    Last edited by seenu_1st; Aug 4th, 2018 at 11:26 AM. Reason: Solved
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width