Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Auto sort routine

Posted on 2011-09-28
9
Medium Priority
?
175 Views
Last Modified: 2012-05-12
EE Professionals,

I have a macro that looks for changes in a column of numbers.  When a number is added to the last column, it automatically "fits" the number into the proper sequence (Low to High).

Example;
1
2
3
4
5

Now if I add 3.2 in the last cell, it will reorder the column as;

1
2
3
3.2
4
5

The problem is, if I add 3.2.1, it will put it at the end of the column instead of after 3.2; apparently not recognizing the expanded requirement for another sub.

Any ideas on how to fix the recognition of the number or position based on this schema?

Thank you,

B.
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 18

Expert Comment

by:nap0leon
ID: 36718938
3.2 is a number
3.2.1 is text

numbers are < text

Change your values from:
1
2
3
3.2
3.2.1
4
5

to
'1
'2
'3
'3.2
'3.2.1
'4
'5

Now auto-sort will work as expected.

It is not enough to change the cell-type to "text", you have to use the single quotes.
There is probably a programmatic way of handling this, but the above is an explanation of what you are seeing.
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36719419
I think to get you the desired result, you may need more. Because your sorted list should be ordered like:

1
2
3
3.2
4
5
.....
9
9.2
10.0
11.2
...

Am I right?

How many decimal points (maximum) you expect to see in your data to be sorted?
Are you okay if the solution uses an additional auxilary column for sorting?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36719548
Which version of Excel?
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:Bright01
ID: 36720422
I'm using Excel 2010.  I'd like to have two decimal points such as in an outline.  I tried to define the cells as "text" but it did not sort correctly with 2 decimal points.

B.
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 36720486
I did the following:

1) Added this function to a regular VBA module:

Function Explode(InputString As String, Optional MaxLevels As Long = 10, Optional LevelDigits As Long = 6)
    
    Dim arr As Variant
    Dim ArrResult() As String
    Dim Counter As Long
    
    arr = Split(InputString, ".")
    ReDim ArrResult(0 To MaxLevels - 1) As String
    
    For Counter = 0 To UBound(ArrResult)
        If Counter <= UBound(arr) Then
            ArrResult(Counter) = Format(arr(Counter), String(LevelDigits, "0"))
        Else
            ArrResult(Counter) = Format(0, String(LevelDigits, "0"))
        End If
    Next
    
    Explode = Join(ArrResult, ".")
    
End Function

Open in new window


2) On a worksheet, I entered these headers into A1:C1...

Item, Text, Helper

3) In A2:A???, I entered various values, such as 1, 2, 3.2, 4.2.7, etc

4) I selected A1:C???, and converted the range into a Table (with header row checked)

5) In B2 I entered this formula...

="Item "&[@Item]

6) In C2 I entered this formula:

=Explode([@Item])

<Both of those formulas should automatically populate for the rest of the Table>

7) I hid Column C to hide the "helper" results, which I use as the sort key

8) I added this code to the sheet module for that worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Me.[a:a]) Is Nothing Then
        Application.EnableEvents = False
        Me.[a1].Sort Key1:=Me.[c1], Order1:=xlAscending, Header:=xlYes
        Application.EnableEvents = True
    End If
    
End Sub

Open in new window





Now, whenever you add a new value at the bottom, or change an existing value in Column A, the table re-sorts the way you want it to.

Please see the attached file for a demonstration.


Q-27346423.xlsm
0
 

Author Comment

by:Bright01
ID: 36720769
matthewspatrick!  This is great!  Thank you....... great job.

B.
0
 

Author Comment

by:Bright01
ID: 36747460
I've requested that this question be closed as follows:

Accepted answer: 0 points for Bright01's comment http:/Q_27346423.html#36720769

for the following reason:

Great solution! &nbsp;You are clearly a &quot;problem solver&quot; or &quot;puzzle master&quot;!<br /><br />B.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36747466
I think you selected your own comment as the answer in error.
0
 

Author Closing Comment

by:Bright01
ID: 36815751
Excellent solution!

Much thanks!

B.
0

Featured Post

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.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

722 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