organizing numbers in excel

I have rows of numbers in Excel between 1 and 53

like
01 05 08 22 32 41
22 32 33 40 45 49
etc
all numbers are in one cell

i want all numbers to be sorted so that if a number is equal to:
01 11 13 17 19 23 29 31 37 41 43 47 53 - write 1
02 04 08 16 22 26 32 34 38 44 46 52 - write 2
03 06 09 12 18 24 27 33 36 39 48 51 - write 3
05 10 15 20 25 30 40 45 50 - write 5
07 14 21 28 35 42 49 - write 7

so if the function or macro is applied to, say,
01 02 15 30 35 47
the output (in the next cell) would be
1 2 5 5 7 1

thanks!


keks_Asked:
Who is Participating?
 
SiddharthRoutCommented:
Is this what you want? Sample attached.

Sid

Code Used

Function GetNumb(SearchNumb As Range, Tbl As Range) As String
    Dim Cl As Range
    Dim MyArray() As String
    Dim Outpt As String
    
    On Error GoTo Whoa
    
    If InStr(SearchNumb.Value, " ") = 0 Then GoTo Whoa
    
    MyArray = Split(SearchNumb, " ")
    
    For i = 0 To UBound(MyArray)
        
        For Each Cl In Tbl
            If Val(Trim(Cl.Value)) = Val(Trim(MyArray(i))) Then
                Outpt = Outpt & " " & Cells(Cl.Row, Tbl.Column - 1).Value
                Exit For
            End If
        Next
    Next i
    GetNumb = Trim(Outpt)
    Exit Function
Whoa:
    GetNumb = 0
End Function

Open in new window

Sample.xls
0
 
SiddharthRoutCommented:
Please find a sample attached. I created a function for you in the sample.

HTH

Sid

Code Used

Function GetNumb(SearchNumb As Range, Tbl As Range) As Long
    Dim Cl As Range
    
    'On Error GoTo Whoa
    For Each Cl In Tbl
        Debug.Print Cl.Address
        If Cl.Value = SearchNumb.Value Then
            GetNumb = Cells(Cl.Row, Tbl.Column - 1).Value
            Exit For
        End If
    Next
    Exit Function
Whoa:
    GetNumb = 0
End Function

Open in new window

Sample.xls
0
 
keks_Author Commented:
My values are stored in the cell as text so the function is giving an error

what should i format the cell as, so the function works? custom? number? # # # # # #? or 0 0 0 0 0 0?

Also your code does not include the range of numbers to be used to sorting. Could anything be missing?

Thanks!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SiddharthRoutCommented:
Can you upload the actual file that is gonna be used?

Sid
0
 
keks_Author Commented:
I just looked at your sample. The thing is, I have all numbers in one cell. All six in one cell separated by space. and I'd like the output all in one cell also.
0
 
SiddharthRoutCommented:
I would appreciate if I can get a sample file to work with. Will help me get the references correct :)

Sid
0
 
keks_Author Commented:
0
 
SiddharthRoutCommented:
keks, It is missing the table?

Sid
0
 
keks_Author Commented:
Not sure what you mean by table

There is no table. I don't think I mentioned any table?
0
 
keks_Author Commented:
looking at your sample, I see what you mean by table. I don't have it mine. I thought that info would be in the code, but feel free to insert it.
0
 
SiddharthRoutCommented:
Ok. :)

Sid
0
 
keks_Author Commented:
Great, can't wait to see it in action.
0
 
keks_Author Commented:
I get 0 when i run it

i noticed it worked in your sample, but when i click 'enable editing' all values turn to 0

any thoughts?
0
 
SiddharthRoutCommented:
Press F2 in the output cell and press Enter. Does it update?

Sid
0
 
keks_Author Commented:
no but i copied my data to your sample file and it works there

thanks!

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.