answer Sort numbers in numerical and string order

  • this is answer for case in link : https://www.ozgrid.com/forum/f…umbers-in-numerical-order


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"ssx.png","data-attachmentid":1219768}[/ATTACH]

  • Just one thing. The "ARD" reference was only an example. Each time I run this code the reference will be different for example it could be GRSD-9005, GRSD-9006, GRSD-9007a etc or A501, A502 A504a, A504b, A505. Could this code be fixed to allow for this?


    Thank you

  • finaly you can make UDF like this
    only small editing in B1=test(A1;"ARD-") change red text with your text

  • Also I am originally pulling these numbers from a range on another sheet and combining them into a string removing blanks. For example Sheet 2 range E16:E416 will be like so:


    E16 = GSA-700
    E17 = BLANK CELL
    E18= BLANK CELL
    E19 = GSA-702
    E20= GSA-700A
    and so on


    My overall goal is to combine these cells into a string remove blanks and duplicates and putting in numerical order separated by a comma as below,


    GSA-700, GSA-700A, GSA-702.


    Hope this helps and thank you again for anything you can do to solve this problem

  • Amazing, thanks very much for this. This is perfect but is there anyway it could run without having to type in the letters each time as I do a lot of these everyday? Also I use a UDF to concatenate the range E16:E416 on another sheet removing blank cells. Could you put this into your code aswell?


    Sorry for being a nuisance.


    Thank you

  • if any range or 1 range edit code

  • My only problem is that there are 20 of these formulas on one sheet which pull numbers from different ranges. So I would have to type in the letters to 20 different formulas each time. Is there a way to do this that isn't a UDF and it would automatically put the numbers in numerical order regardless of which letters there are?


    Thank you

  • 1 minute ago
    My only problem is that there are 20 of these formulas on one sheet which pull numbers from different ranges. So I would have to type in the letters to 20 different formulas each time. Is there a way to do this that isn't a UDF and it would automatically put the numbers in numerical order regardless of which letters there are?


    if you has any or multi range using parramArray

    Code
    Function test(Textreplace As String, fx As Variant) As String
    change to  
    [B]Function test1(Textreplace As String, ParamArray fx() As Variant) As String[/B]
    but you must edit little code you can do in cell with multi range and multi sheet
    [B]= test1("A",shee1A1:a10,sheet2b10:b30,{"F90";"F90";"F90A"})

    [/B]
    [/code]

  • How does this work?


    on sheet 1 cell C23 I need the formula =test("GSA-",sheet2,E16:E416)



    on sheet 1 cell C13 I need the formula =test("GSA-",sheet2,E520:E920)



    on sheet 1 cell C33 I need the formula =test("GSA-",sheet2,E1100:E1500)


    and so on


  • i dont understand you want
    in post #7 i has edit my code
    C23=test("GSA-",sheet2,E16:E416)
    C13=test("GSA-",sheet2E520:E920)
    C33=test("GSA-",sheet2,E1100:E1500)
    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"cxx.png","data-attachmentid":1219785}[/ATTACH]

  • yes your code works perfectly. I was just wondering if it could be adjusted so I did not have to keep retyping the letters into the formulas everytime they changed. Could the code not just order the numbers and disregard the letters in the string?


    job 1 the letters could be GSA-
    job 2 the letters could be ARD-
    job 3 the letters could be TRSQ
    etc

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!