Quick method to sort a list of strings?
 

I have code that was written (to my internal customer specifications) which
would churn through a list of information and populate a report worksheet.
Part of the report is that the code adds comments to different cells; each
cell may have no comment at all, a single line comment, or multiple lines of
comments. For example, the cell may represent the number of purchases by
customer X in the month of October (N=4); the comment would then list the
needed details for each of those four purchases ("W331 product, 200 units.
Ordered Oct 4, shipped Oct 7" & vbcrlf & "C121 product, 150 units, etc...).

Problem: the customer has now specified the need to have those comments
appear in alpha order, whereas I was ordering them in the order in which
they were found. I do not have all the these comments stored in an array
(that would be a nightmare to reprogram it now)- I just append the text as
I'm processing source files.

Is there a relatively easy way to (after the fact) cycle through every cell,
and if it has more than one line of comments, split those lines out into
separate entities and sort them in alpha-numeric order?

I can grab the comment pretty easily, and can probably figure out an
ineloquent way to split into the individual strings, but I'm not sure the
best way to sort. Should I paste each set over to a new sheet and sort them,
or is there a fast and easy way to just do it in memory?

Thanks!
Keith

Quick method to sort a list of strings?
 

Will this code help? Split works nicely.

'split string into an array around so each line is a seperate item
a = Split(Range("B1").Comment, Chr(10))

'sort Strings
For i = 0 To (UBound(a) - 1)
For j = (i + 1) To UBound(a)
If StrComp(a(i), a(j)) = 1 Then
temp = a(i)
a(i) = a(j)
a(j) = temp
End If
Next j
Next i
'put string back together
b = ""
For i = 0 To UBound(a)
If b = "" Then
b = a(i)
Else
b = b & Chr(10) & a(i)
End If
Next i

Range("B1").Comment = b


"ker_01" wrote:


I have code that was written (to my internal customer specifications) which
would churn through a list of information and populate a report worksheet.
Part of the report is that the code adds comments to different cells; each
cell may have no comment at all, a single line comment, or multiple lines of
comments. For example, the cell may represent the number of purchases by
customer X in the month of October (N=4); the comment would then list the
needed details for each of those four purchases ("W331 product, 200 units.
Ordered Oct 4, shipped Oct 7" & vbcrlf & "C121 product, 150 units, etc...).

Problem: the customer has now specified the need to have those comments
appear in alpha order, whereas I was ordering them in the order in which
they were found. I do not have all the these comments stored in an array
(that would be a nightmare to reprogram it now)- I just append the text as
I'm processing source files.

Is there a relatively easy way to (after the fact) cycle through every cell,
and if it has more than one line of comments, split those lines out into
separate entities and sort them in alpha-numeric order?

I can grab the comment pretty easily, and can probably figure out an
ineloquent way to split into the individual strings, but I'm not sure the
best way to sort. Should I paste each set over to a new sheet and sort them,
or is there a fast and easy way to just do it in memory?

Thanks!
Keith

Quick method to sort a list of strings?
 

Joel-
That worked awesomely. Thank you!

"Joel" wrote in message
...
Will this code help? Split works nicely.

'split string into an array around so each line is a seperate item
a = Split(Range("B1").Comment, Chr(10))

'sort Strings
For i = 0 To (UBound(a) - 1)
For j = (i + 1) To UBound(a)
If StrComp(a(i), a(j)) = 1 Then
temp = a(i)
a(i) = a(j)
a(j) = temp
End If
Next j
Next i
'put string back together
b = ""
For i = 0 To UBound(a)
If b = "" Then
b = a(i)
Else
b = b & Chr(10) & a(i)
End If
Next i

Range("B1").Comment = b


"ker_01" wrote:


I have code that was written (to my internal customer specifications)
which
would churn through a list of information and populate a report
worksheet.
Part of the report is that the code adds comments to different cells;
each
cell may have no comment at all, a single line comment, or multiple lines
of
comments. For example, the cell may represent the number of purchases by
customer X in the month of October (N=4); the comment would then list the
needed details for each of those four purchases ("W331 product, 200
units.
Ordered Oct 4, shipped Oct 7" & vbcrlf & "C121 product, 150 units,
etc...).

Problem: the customer has now specified the need to have those comments
appear in alpha order, whereas I was ordering them in the order in which
they were found. I do not have all the these comments stored in an array
(that would be a nightmare to reprogram it now)- I just append the text
as
I'm processing source files.

Is there a relatively easy way to (after the fact) cycle through every
cell,
and if it has more than one line of comments, split those lines out into
separate entities and sort them in alpha-numeric order?

I can grab the comment pretty easily, and can probably figure out an
ineloquent way to split into the individual strings, but I'm not sure the
best way to sort. Should I paste each set over to a new sheet and sort
them,
or is there a fast and easy way to just do it in memory?

Thanks!
Keith