Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort order of a collection
I have created a collection of values from my worksheet using John
Walkenback's cited NoDupes method as below: Set Allcells _ = Intersect(Range("B:M"), Range("B3,B6,B9,B12,B15,B18,B21,B24").EntireRow) On Error Resume Next For Each cell In Allcells If cell.Value = "" Then 'do nothing Else NoDupes.Add cell.Value, CStr(cell.Value) End If Next cell 'Sort the collection For i = 1 To NoDupes.Count - 1 For j = i + 1 To NoDupes.Count If NoDupes(i) NoDupes(j) Then swap1 = NoDupes(i) swap2 = NoDupes(j) NoDupes.Add swap1, befo=j NoDupes.Add swap2, befo=i NoDupes.Remove i + 1 NoDupes.Remove j + 1 End If Next j Next i The collection is then copied into another worksheet. However the collection is sorting the items in the list correctly, but not in numerical order. For example I have the following: DNA_1 DNA_2 DNA_3 DNA_10 DNA_11 the sort collection codes puts them in the following order DNA_1 DNA_10 DNA_11 DNA_2 DNA_3 where I want them to be in numerical order. i.e. DNA_1,DNA_2,DNA_3, etc. Can anyone guide me on how I can achieve this please? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort order of a collection
if all data has a format like [**** & "_" & number], then i would change the
code below If NoDupes(i) NoDupes(j) Then to If Val(Split(NoDupes(i), "_")(1)) Val(Split(NoDupes(j), "_")(1)) Then keizi "simon" wrote in message ups.com... I have created a collection of values from my worksheet using John Walkenback's cited NoDupes method as below: Set Allcells _ = Intersect(Range("B:M"), Range("B3,B6,B9,B12,B15,B18,B21,B24").EntireRow) On Error Resume Next For Each cell In Allcells If cell.Value = "" Then 'do nothing Else NoDupes.Add cell.Value, CStr(cell.Value) End If Next cell 'Sort the collection For i = 1 To NoDupes.Count - 1 For j = i + 1 To NoDupes.Count If NoDupes(i) NoDupes(j) Then swap1 = NoDupes(i) swap2 = NoDupes(j) NoDupes.Add swap1, befo=j NoDupes.Add swap2, befo=i NoDupes.Remove i + 1 NoDupes.Remove j + 1 End If Next j Next i The collection is then copied into another worksheet. However the collection is sorting the items in the list correctly, but not in numerical order. For example I have the following: DNA_1 DNA_2 DNA_3 DNA_10 DNA_11 the sort collection codes puts them in the following order DNA_1 DNA_10 DNA_11 DNA_2 DNA_3 where I want them to be in numerical order. i.e. DNA_1,DNA_2,DNA_3, etc. Can anyone guide me on how I can achieve this please? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort order of a collection
Keizi,
Thank you very much. This worked perfectly. Could you explain to me what the reformatted line is doing? Simon On 2 Mar, 10:41, "kounoike" wrote: if all data has a format like [**** & "_" & number], then i would change the code below If NoDupes(i) NoDupes(j) Then to If Val(Split(NoDupes(i), "_")(1)) Val(Split(NoDupes(j), "_")(1)) Then keizi "simon" wrote in message ups.com... I have created a collection of values from my worksheet using John Walkenback's cited NoDupes method as below: Set Allcells _ = Intersect(Range("B:M"), Range("B3,B6,B9,B12,B15,B18,B21,B24").EntireRow) On Error Resume Next For Each cell In Allcells If cell.Value = "" Then 'do nothing Else NoDupes.Add cell.Value, CStr(cell.Value) End If Next cell 'Sort the collection For i = 1 To NoDupes.Count - 1 For j = i + 1 To NoDupes.Count If NoDupes(i) NoDupes(j) Then swap1 = NoDupes(i) swap2 = NoDupes(j) NoDupes.Add swap1, befo=j NoDupes.Add swap2, befo=i NoDupes.Remove i + 1 NoDupes.Remove j + 1 End If Next j Next i The collection is then copied into another worksheet. However the collection is sorting the items in the list correctly, but not in numerical order. For example I have the following: DNA_1 DNA_2 DNA_3 DNA_10 DNA_11 the sort collection codes puts them in the following order DNA_1 DNA_10 DNA_11 DNA_2 DNA_3 where I want them to be in numerical order. i.e. DNA_1,DNA_2,DNA_3, etc. Can anyone guide me on how I can achieve this please?- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort order of a collection
Hi simon
Say NoDupes(i) is "DNA_2" and NoDupes(j) is "DNA_10", then "If NoDupes(i) NoDupes(j) Then NoDupes(j)" means If "DNA_2" "DNA_10" Then and this comes TRUE, so "DNA_10" comes before "DNA_2" swapping two data. Split function returns a zero-based, one-dimensional array containing a specified number of substrings.(look up in VBA help for more details) Split(NoDupes(i), "_") and Split(NoDupes(j), "_") returns array {"DNA", "2"}, {"DNA", "10"}respectively, so Split(NoDupes(i), "_") (1) returns a string of "2" and Split(NoDupes(j), "_")(1) returns a string of "10". For compareing two data as number, i used Val function like Val(Split(NoDupes(i), "_")(1)) to change string to number. this makes "DNA_2" come before "DNA_10". But this only works in the case that the string before "_" is the same in all data because comparing two data only using the string after "_". if this is not the case, you need to take other way. keizi "simon" wrote in message oups.com... Keizi, Thank you very much. This worked perfectly. Could you explain to me what the reformatted line is doing? Simon On 2 Mar, 10:41, "kounoike" wrote: if all data has a format like [**** & "_" & number], then i would change the code below If NoDupes(i) NoDupes(j) Then to If Val(Split(NoDupes(i), "_")(1)) Val(Split(NoDupes(j), "_")(1)) Then keizi "simon" wrote in message ups.com... I have created a collection of values from my worksheet using John Walkenback's cited NoDupes method as below: Set Allcells _ = Intersect(Range("B:M"), Range("B3,B6,B9,B12,B15,B18,B21,B24").EntireRow) On Error Resume Next For Each cell In Allcells If cell.Value = "" Then 'do nothing Else NoDupes.Add cell.Value, CStr(cell.Value) End If Next cell 'Sort the collection For i = 1 To NoDupes.Count - 1 For j = i + 1 To NoDupes.Count If NoDupes(i) NoDupes(j) Then swap1 = NoDupes(i) swap2 = NoDupes(j) NoDupes.Add swap1, befo=j NoDupes.Add swap2, befo=i NoDupes.Remove i + 1 NoDupes.Remove j + 1 End If Next j Next i The collection is then copied into another worksheet. However the collection is sorting the items in the list correctly, but not in numerical order. For example I have the following: DNA_1 DNA_2 DNA_3 DNA_10 DNA_11 the sort collection codes puts them in the following order DNA_1 DNA_10 DNA_11 DNA_2 DNA_3 where I want them to be in numerical order. i.e. DNA_1,DNA_2,DNA_3, etc. Can anyone guide me on how I can achieve this please?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retaining Sort order in the Sort Dialog box | Excel Discussion (Misc queries) | |||
Sort sheet based on particuilar sort order | Excel Worksheet Functions | |||
Sort collection string keys | Excel Programming | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming | |||
sort order | Excel Discussion (Misc queries) |