Best way to sort

[Roldy]

Suppose I have a list of structural beams.

W6x15
W24x207
W6x15

What I want to accomplish is to have a new list of the beams in descending order of the depth first, W, and then the weight per foot, number after x. My initial attempt was create a main dictionary containing all the W sizes. The W sizes would themselves be a dictionary where each would contain the weight per foot value. So essentially I would have a dictionary with two dictionaries 6 and 24. Dictionary 6 would have items 15 and 207. I figure that for each W dictionary I can write a piece of code that would sort in descending order the weight per foot. Then in the main dictionary I can sort on the W sizes in descending order. Is this a good approach or is there something better I could do?

[arnelgp]

just create two more columns and split your W6x15 description. one column would contain 6 and the other 15.
then sort on to this two columns.

to convert to numbers, respectively:

first column:

=VALUE(LEFT(SUBSTITUTE(A2,"W",""),FIND("x",SUBSTITUTE(A2,"W",""))-1))

second column:

=VALUE(MID(A2,FIND("x",A2)+1,99))

[Roldy]

just create two more columns and split your W6x15 description. one column would contain 6 and the other 15.
then sort on to this two columns.

to convert to numbers, respectively:

first colum:

=VALUE(LEFT(SUBSTITUTE(A2,"W",""),FIND("x",SUBSTITUTE(A2,"W",""))-1))

second column:

=VALUE(MID(A2,FIND("x",A2)+1,99))

I failed to mention that I need to do this programmatically due to the program environment I'm in. I am not using Excel. I'm using VBA within another program. I couldn't find a strictly VBA sub forum to put this question in.

[arnelgp]

i thought it is excel because you post it in Excel forum?
then what VBA program are you using?

[Roldy]

i thought it is excel because you post it in Excel forum?
then what VBA program are you using?

I'm using a CAD program called SolidWorks. I have the ability to write macros to perform automated tasks using VBA. The task at hand is to collect the structural members and sort. Where should I post VBA related questions on this forum? I couldn't find the appropriate location.

[Dave]

Hi Roldy. You can trial this VBA. HTH. Dave

Sub test()
    Dim Cnt As Integer, Cnt1 As Integer, Cnt2 As Integer, Rng As Range
    Dim Lastrow As Integer, NameArr() As Variant, Cnter As Integer
    Dim TempStr As String, Splitter As Variant, Depth As Double
    ' Data in "A1"A" & etc)
    ' Depth output in "C"; Wt/ft in "D"
    With Sheets("Sheet1")
        Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        ' sort unique into array
        For Cnt = 1 To Lastrow
            For Cnt1 = 1 To (Cnt - 1)
                If LCase(.Range("A" & Cnt1).Value) = _
                    LCase(.Range("A" & Cnt).Value) Then ' more than one entry
                    GoTo Bart
                End If
            Next Cnt1
            Cnter = Cnter + 1
            ReDim Preserve NameArr(Cnter)
            NameArr(Cnter - 1) = .Range("A" & Cnt).Value
            Bart:
        Next Cnt
        On Error GoTo ErFix
        Application.ScreenUpdating = False
        ' loop unique arr & split into depth("C") and wt/ft("D")
        For Cnt2 = LBound(NameArr) To UBound(NameArr) - 1
            TempStr = NameArr(Cnt2) & "x"
            Splitter = Split(TempStr, "x")
            Depth = CDbl(Right(Splitter(0), Len(Splitter(0)) - 1))
            .Range("C" & Cnt2 + 1).Value = Depth
            .Range("D" & Cnt2 + 1).Value = Splitter(1) 'wt/ft
        Next Cnt2
        ' sort descending
        Set Rng = .Range(.Cells(1, "C"), .Cells(Lastrow, "D"))
        Rng.Sort Key1:=Range("C1"), Order1:=xlDescending, Orientation:=xlSortColumns
        ' Add "W" to depth
        For Cnt2 = LBound(NameArr) To UBound(NameArr) - 1
            .Range("C" & Cnt2 + 1).Value = "W" & .Range("C" & Cnt2 + 1).Value
        Next Cnt2
    End With
    ErFix:
    Application.ScreenUpdating = True
End Sub

[Roldy]

Hi Roldy. You can trial this VBA. HTH. Dave

Sub test()
    Dim Cnt As Integer, Cnt1 As Integer, Cnt2 As Integer, Rng As Range
    Dim Lastrow As Integer, NameArr() As Variant, Cnter As Integer
    Dim TempStr As String, Splitter As Variant, Depth As Double
    ' Data in "A1"A" & etc)
    ' Depth output in "C"; Wt/ft in "D"
    With Sheets("Sheet1")
        Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        ' sort unique into array
        For Cnt = 1 To Lastrow
            For Cnt1 = 1 To (Cnt - 1)
                If LCase(.Range("A" & Cnt1).Value) = _
                     LCase(.Range("A" & Cnt).Value) Then ' more than one entry
                    GoTo Bart
                End If
            Next Cnt1
            Cnter = Cnter + 1
            ReDim Preserve NameArr(Cnter)
            NameArr(Cnter - 1) = .Range("A" & Cnt).Value
            Bart:
        Next Cnt
        On Error GoTo ErFix
        Application.ScreenUpdating = False
        ' loop unique arr & split into depth("C") and wt/ft("D")
        For Cnt2 = LBound(NameArr) To UBound(NameArr) - 1
            TempStr = NameArr(Cnt2) & "x"
            Splitter = Split(TempStr, "x")
            Depth = CDbl(Right(Splitter(0), Len(Splitter(0)) - 1))
            .Range("C" & Cnt2 + 1).Value = Depth
            .Range("D" & Cnt2 + 1).Value = Splitter(1) 'wt/ft
        Next Cnt2
        ' sort descending
        Set Rng = .Range(.Cells(1, "C"), .Cells(Lastrow, "D"))
        Rng.Sort Key1:=Range("C1"), Order1:=xlDescending, Orientation:=xlSortColumns
        ' Add "W" to depth
        For Cnt2 = LBound(NameArr) To UBound(NameArr) - 1
            .Range("C" & Cnt2 + 1).Value = "W" & .Range("C" & Cnt2 + 1).Value
        Next Cnt2
    End With
    ErFix:
    Application.ScreenUpdating = True
End Sub

I've decided on using a dictionary to store a class objects. Each class object defines a structural member. For example, a class object for a W6 x 25 beam would look something like this:
size1 = 6
size2 = 25
size3 = 0
vweldSize = "W6 X 25"
vweldType = "W Section"

When my code gathers up all the structural members, it also creates a collection of the unique types in my file; W Section, L Angle, C Channel, etc.

For each memberType in the memberType collection I look at every gathered member and put those into a temporary collection. After this temporary collection is created, it contains all the members of type "W Section" for example. I then need to sort this collection based on the class objects size1 then by size2 and by size3. This last part is where I'm a little stuck.

[Roldy]

My solution is as follows for the temporary collection sorting:

For icounter1 = 1 To tempColl.Count - 1
    For icounter2 = icounter1 + 1 To tempColl.Count
        If tempColl(icounter1).size1 <= tempColl(icounter2).size1 Then
            If tempColl(icounter1).size2 <= tempColl(icounter2).size2 Then
                Set tempItem = tempColl(icounter2)
                tempColl.Remove icounter2
                tempColl.Add tempItem, , icounter1
            End If
        End If
    Next icounter2
Next icounter1

This doesn't have a check for comparing size3's but it shouldn't be any different logic.