# 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!

###### Who is Participating?

Commented:
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
``````
Sample.xls
0

Commented:
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
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
``````
Sample.xls
0

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

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

Sid
0

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

Commented:
I would appreciate if I can get a sample file to work with. Will help me get the references correct :)

Sid
0

Author Commented:
0

Commented:
keks, It is missing the table?

Sid
0

Author Commented:
Not sure what you mean by table

There is no table. I don't think I mentioned any table?
0

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

Commented:
Ok. :)

Sid
0

Author Commented:
Great, can't wait to see it in action.
0

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

Commented:
Press F2 in the output cell and press Enter. Does it update?

Sid
0

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.