Sort in striing?

[Charlotte E.]

Hi,


I have a string variable, containing names seperated by a comma, like:

NameString = "Peter, Dan, Hans, Carl, Bruce" etc...

I would like the string to by sorted, by the names between the commas,
so my string end up like:

NameString = "Bruce, Carl, Dan, Hans, Peter" etc...

And to make things worse, the number of commas/names can be different
from time to time...

Can anyone help me acomplish this?


Thanks in advance,

CE

 

[Ron Rosenfeld]

Hi,


I have a string variable, containing names seperated by a comma, like:

NameString = "Peter, Dan, Hans, Carl, Bruce" etc...

I would like the string to by sorted, by the names between the commas,
so my string end up like:

NameString = "Bruce, Carl, Dan, Hans, Peter" etc...

And to make things worse, the number of commas/names can be different
from time to time...

Can anyone help me acomplish this?


Thanks in advance,

CE

There are lots of VBA sort routines out there. Here's one:

===========================
Option Explicit
Sub SortString()
Dim NameString As String
Dim v As Variant
NameString = "Peter, Dan, Hans, Carl, Bruce"
v = Split(NameString, ", ")
Quick_Sort v, LBound(v), UBound(v)

Debug.Print Join(v, ", ")
End Sub


'--------------------------------------------
Sub Quick_Sort(ByRef SortArray As Variant, ByVal First As Long, ByVal Last As Long)
Dim Low As Long, High As Long
Dim Temp As Variant, List_Separator As Variant
Low = First
High = Last
List_Separator = SortArray((First + Last) / 2)
Do
Do While (SortArray(Low) < List_Separator)
Low = Low + 1
Loop
Do While (SortArray(High) > List_Separator)
High = High - 1
Loop
If (Low <= High) Then
Temp = SortArray(Low)
SortArray(Low) = SortArray(High)
SortArray(High) = Temp
Low = Low + 1
High = High - 1
End If
Loop While (Low <= High)
If (First < High) Then Quick_Sort SortArray, First, High
If (Low < Last) Then Quick_Sort SortArray, Low, Last
End Sub
=================================

 

[Bruno Campanini]

Charlotte E. laid this down on his screen :

Hi,


I have a string variable, containing names seperated by a comma, like:

NameString = "Peter, Dan, Hans, Carl, Bruce" etc...

I would like the string to by sorted, by the names between the commas, so my
string end up like:

NameString = "Bruce, Carl, Dan, Hans, Peter" etc...

And to make things worse, the number of commas/names can be different from
time to time...

Can anyone help me acomplish this?

=========================
Public Sub SortInString()
Dim S As String, S1, Swap As String
Dim i As Integer, j As Integer

S = "Peter, Dan, Hans, Carl, Bruce"
S1 = Split(Trim(S) & ",", ",")
S = ""
For i = 0 To UBound(S1) - 2
For j = i + 1 To UBound(S1) - 1
If S1(j) < S1(i) Then
Swap = S1(i)
S1(i) = S1(j)
S1(j) = Swap
End If
Next
S = S & S1(i) & ", "
Next
S = S & S1(i)
MsgBox S

End Sub
===========================

Bruno

 

[Auric__]

Charlotte E. laid this down on his screen :

=========================
Public Sub SortInString()
Dim S As String, S1, Swap As String
Dim i As Integer, j As Integer

S = "Peter, Dan, Hans, Carl, Bruce"
S1 = Split(Trim(S) & ",", ",")
S = ""
For i = 0 To UBound(S1) - 2
For j = i + 1 To UBound(S1) - 1
If S1(j) < S1(i) Then
Swap = S1(i)
S1(i) = S1(j)
S1(j) = Swap
End If
Next
S = S & S1(i) & ", "
Next
S = S & S1(i)
MsgBox S

End Sub
===========================

I don't know what the purpose of adding a blank entry at the end of the
string is for, but it's not necessary to sort. Also, Swap is a keyword (see
below). Also also, the Join keyword makes it unnecessary to manually
recombine the array back into the string.

If you use this method, do this instead:

Public Function SortInString(S As String, _
Optional Delimiter As String = ", ")
Dim S1 As Variant
Dim i As Long, j As Long

S1 = Split(Trim(S), Delimiter)
For i = 0 To UBound(S1) - 1
For j = i + 1 To UBound(S1)
If S1(j) < S1(i) Then Swap S1(i), S1(j)
Next
Next
S = Join(S1, Delimiter)
SortInString = S
End Function

Use it like so:

sorted = SortInString("Peter, Dan, Hans, Carl, Bruce")
or
sorted = SortInString("Peter, Dan, Hans, Carl, Bruce", ", ")

However, the method Ron Rosenfeld posted (using Quicksort) is probably
faster.

 

[Bruno Campanini]

Auric__ submitted this idea :

[...]

However, the method Ron Rosenfeld posted (using Quicksort) is probably
faster.

Yes, faster if you are using hundreds or thousands of elemnts in one
string.
But, is it the case of a string which resides in one Excel cell?

Don't waste your time...

Bruno

 

[Ron Rosenfeld]

However, the method Ron Rosenfeld posted (using Quicksort) is probably
faster.

The Quicksort algorithm should be faster unless the list is already nearly sorted. With short lists, however, the speed difference is probably not significant compared with a Bubblesort.

 

[Charlotte E.]

Thanks guys - got it working

CE



Den 22.11.2012 11:21, Charlotte E. skrev:

 

[GS]

I'm going to say that I've done exhaustive tests with various sorting
solutions and found none faster than dumping the list into a temp wks
and using Excel's Sort function followed by dumping the result back
into an array for processing, or into a ListBox if appropriate.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

I'm going to say that I've done exhaustive tests with various sorting
solutions and found none faster than dumping the list into a temp wks
and using Excel's Sort function followed by dumping the result back
into an array for processing, or into a ListBox if appropriate.

I agree. And for multicolumn sorts, that is my preference. But for single or two column sorts, the coding seems simpler (for me) to just do it in VBA.

 

[GS]

Ron Rosenfeld formulated on Friday :

I agree. And for multicolumn sorts, that is my preference. But for single
or two column sorts, the coding seems simpler (for me) to just do it in VBA.

I agree.., as long as the lists are relatively short. I typically store
lists on a hidden sheet that has named ranges for each list. I usually
leave them sorted after adding/removing items so they're 'ready-to-use'
however I need/want. Different case, though, for working with list
items on the fly.<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion