Solved

# Auto sort routine

Posted on 2011-09-28
167 Views
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
Question by:Bright01

LVL 18

Expert Comment

ID: 36718938
3.2 is a number
3.2.1 is text

numbers are < text

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

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 92

Expert Comment

ID: 36719548
Which version of Excel?
0

Author Comment

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 92

Accepted Solution

Patrick Matthews earned 500 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
``````

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
Application.EnableEvents = True
End If

End Sub
``````

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

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

B.
0

Author Comment

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 92

Expert Comment

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

Author Closing Comment

ID: 36815751
Excellent solution!

Much thanks!

B.
0

## Featured Post

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.