Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

organizing numbers in excel

Posted on 2011-04-20
15
Medium Priority
?
231 Views
Last Modified: 2012-05-11
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!


0
Comment
Question by:keks_
  • 8
  • 7
15 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35438274
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
 

Author Comment

by:keks_
ID: 35438623
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35438626
Can you upload the actual file that is gonna be used?

Sid
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:keks_
ID: 35438635
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
 
LVL 30

Expert Comment

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

Sid
0
 

Author Comment

by:keks_
ID: 35438657
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35438664
keks, It is missing the table?

Sid
0
 

Author Comment

by:keks_
ID: 35438681
Not sure what you mean by table

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

Author Comment

by:keks_
ID: 35438685
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35438737
Ok. :)

Sid
0
 

Author Comment

by:keks_
ID: 35438741
Great, can't wait to see it in action.
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35438761
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
 

Author Comment

by:keks_
ID: 35438802
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35438818
Press F2 in the output cell and press Enter. Does it update?

Sid
0
 

Author Comment

by:keks_
ID: 35438843
no but i copied my data to your sample file and it works there

thanks!

0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question