Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Method for protecting cells which still allows quick editing | Excel Discussion (Misc queries) | |||
Ontime method, quick question. | Excel Programming | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
Quick method to add absolute references in Excel using keyboard | Excel Discussion (Misc queries) | |||
Quick VBA method for looking up data | Excel Programming |