Sort - Three Variations

[Papi]

Is it possible to sort using the three criteria in the noted sequence?

1) UPPER CASE
2) Bold (Proper)
3) Italics (Proper)

 

[Papi]

Try this:


Hello TKB,

That is great. It is really coming together. Thanks for the explanation as well.

A couple things. I do not know much about looping but I added a line at the end of your code so that once finished the code it then runs ActiveCell.Offset(0, 2).Range("A1").Select ...to move it over to the next column so I just press the shortcut again until is is done. I setup groups so the blank columns are there but hidden away. I recall some code from VoG to repeat a macro and need to dig into the archives and include it in this setcion so that it goes across from left to right and runs the macro as many times as there are columns.

I noticed when it sorts that it sorts alphabeticaly something like this

Bold 10
Bold 3
Bold 6

Is there a way to sort the 3, 6 and 10 in this sequence rather than 10, 3, 6?

 

[TKB]

In the column your sorting... are they words, or are they numbers?

 

[Papi]

They are words.

 

[TKB]

Could you send me a screenshot of what your seeing? Im not able to replicate the issue.

Thanks!

 

[Papi]

If I sort the items below they start with 10 then 12 then 3. This may just be how Excel is handling text with numbers. What I hoped for was the items sorted the way the second set of data is shown.

first set
WALNUTS 10 Oz
WALNUTS 12 Oz
WALNUTS 3 Oz
WALNUTS 5 Oz

sesond set
WALNUTS 3 Oz
WALNUTS 5 Oz
WALNUTS 10 Oz
WALNUTS 12 Oz

 

[mikerickson]

As I understand it, you are trying to redfine "ascending" to mean

UpperCase+Bold+Italic < UpperCase+Bold < UC+Italic < UC < Proper+Bold+Italic < Proper+Bold < Proper+Italic < Proper < everything else

You could do something like this

Sub test()
    Dim oneCol as Range
    For Each oneCol in Range("A1:D45").Columns
        Call FormatSort(oneCol)
    Next oneCol
End Sub

Sub FormatSort(sourceRange As Range, Optional destRange As Range)
    Dim arrData As Variant
    Dim i As Long
    
    If destRange Is Nothing Then Set destRange = sourceRange
    arrData = sourceRange.Value
    For i = 1 To sourceRange.Rows.Count
        If Len(arrData(i, 1)) <> 0 Then
            arrData(i, 1) = UBIprefix(sourceRange.Cells(i, 1)) & arrData(i, 1)
        End If
    Next i
    
    sourceRange.Copy Destination:=destRange.Resize(sourceRange.Rows.Count, 1)
    
    With destRange.Resize(sourceRange.Rows.Count, 1)
        sourceRange.Copy Destination:=.Cells
        .Value = arrData
        .Sort key1:=.Cells(1, 1), order1:=xlAscending
        arrData = .Value
        For i = 1 To .Rows.Count
            arrData(i, 1) = Mid(arrData(i, 1), 4)
        Next i
        .Value = arrData
    End With
    
End Sub

Function UBIprefix(ByRef aRange As Range)
    Const chrNot = "Z"
    Set aRange = aRange.Cells(1, 1)
    With aRange
        If UCase(.Text) = .Text Then
            UBIprefix = "A"
        ElseIf Application.Proper(.Text) = .Text Then
            UBIprefix = "B"
        Else
            UBIprefix = chrNot
        End If
        If .Font.Bold Then
            UBIprefix = UBIprefix & "B"
        Else
            UBIprefix = UBIprefix & chrNot
        End If
        If .Font.Italic Then
            UBIprefix = UBIprefix & "i"
        Else
            UBIprefix = UBIprefix & chrNot
        End If
    End With
End Function

About the Walnut 10 oz., Walnut 3 oz. issue. Numerals (strings that look like numbers) are alphabetized not sorted according to number. Inserting leading zeros (Walnut 03 oz.) fixes the problem.

 

[Papi]

Thanks for all the help.