Sort column 2 different ways.

Hello all,

Hope someone can help me with my problem.

I need to sort The same column 2 different ways.

For the first i Need to sort by "SIM" THEN "NO"

INSIDE YES, sort by name and then measure.
INSIDE NO, sort only by measure.

Hope you can help me.

Originally Posted by Richard Buttrey

I wonder if you really mean SORT as we all understand it. If so can't you just sort with Brand as the primary key sorted descending, and Name as the secondary key ascending.

If not explain how you get A, A, B, C with 20, 30, 60, 85 in H2:H5 when these are nor shown in the A1:C9 table.

Hello Mr. Richard,

Thanks in advance for your help.

I mean sort as you all understand.

Understand for the BRAND "YES" i need to sort by name and then by measures.

For the brand "NO" i just need to sort by measures, measures are priority.

About the table, it was wrong.
BRAND NAME MEASURE
YES A 20
YES A 85
YES B 30
YES C 60
NO G 15
NO E 26
NO Z 50
NO F 60

This is what i need to have. if yes, sort name then measure.
If no, sort by measure, and put name according

Originally Posted by Richard Buttrey

...in that case just add a third sort level and sort on MEASURE ascending.

Although if if do everything together it will sort the same for YES And NO.

I have this:

'Ordenação da coluna do Marca, inclui toda a coluna, desde o Cabeçalho (INCLUIDO!)
Public Const OrdenarMarca As String = "M:M"

'Ordenação da coluna do Nome Flexografia, inclui toda a coluna, desde o Cabeçalho (INCLUIDO!)
Public Const OrdenarNomeFlexo As String = "P:P" 

'É a Coluna da largura do tapasol desde o inicio do quadro até ao fim
Public Const LarguraTapasolQuadro As String = "X:X"

'Ordenação Personalizada
Dim LR  As Long
Dim WkRg  As Range
Const WkSheet As String = "Tapasois"
    LR = Range(Coluna1 & Rows.Count).End(xlUp).Row
    
    With ActiveWorkbook.Worksheets(WkSheet)
        .Sort.SortFields.Clear
                
        .Sort.SortFields.Add Key:=Range(OrdenarMarca), _
            SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="SIM,NÃO", _
            DataOption:=xlSortNormal

        .Sort.SortFields.Add Key:=Range(OrdenarNomeFlexo) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal


        .Sort.SortFields.Add Key:=Range(LarguraTapasolQuadro) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            
            With .Sort
                .SetRange Range(ColunasQuadroCabeçalho & LR)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

Does this help?

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).row
Range(Cells(2, 1), Cells(x, 3)).Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range("B2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending
Set r = Columns(1).Find("NO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(r.row, "A"), Cells(x, "C")).Sort Key1:=Cells(r.row, "C"), Order1:=xlAscending 
 End If
Set r = Nothing
    
End Sub

Originally Posted by JOHN H. DAVIS

Does this help?

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).row
Range(Cells(2, 1), Cells(x, 3)).Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range("B2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending
Set r = Columns(1).Find("NO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(r.row, "A"), Cells(x, "C")).Sort Key1:=Cells(r.row, "C"), Order1:=xlAscending 
 End If
Set r = Nothing
    
End Sub

Just clarify me some things:

What does this means?

x = Range("A" & Rows.Count).End(3).row

What is the 3 ?

Where is the sort for YES first then No? here:

Range(Cells(2, 1), Cells(x, 3)).Sort Key1:=Range("A2"), Order1:=xlDescending

?

ignore this

I've sent you a sample in this comment of my file.

Can you please help me out modifying the initial code to meet my goal?

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).row
Range(Cells(2, 1), Cells(x, 3)).Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range("B2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending
Set r = Columns(1).Find("NO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(r.row, "A"), Cells(x, "C")).Sort Key1:=Cells(r.row, "C"), Order1:=xlAscending 
 End If
Set r = Nothing
    
End Sub

The brand is column: M
The Name is column: N
The measures is column: X

x Stands for the last cell with data in Column "A"

  
x = Range("A" & Rows.Count).End(3).row

(3) is a short form for xlup - Means the same (3) or (xlUp)

When you sort the column "A" descending Alphabetically the YES's will be on Top and then the "NO's".

Did you test the code too see if it does what you want?

My problem is that i will filter this first, so this needs to be flexible.

I think if i put:

Range(Cells(2, 1) ....

The 2 means the 1st data row and column correct?

Cells(x, 3)

The 3 means the Total columns of data correct?

I already tested the code on the easy workbook, now i'm putting in the large one after filts and see what's going on

Maybe:

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("N7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(1).Find("NO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(r.row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.row, "X"), Order1:=xlAscending ', Key2:=Cells(r.row, "B"), Order2:=xlAscending
    End If
Set r = Nothing
    
End Sub

Originally Posted by JOHN H. DAVIS

Maybe:

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("N7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(1).Find("NO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(r.row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.row, "X"), Order1:=xlAscending ', Key2:=Cells(r.row, "B"), Order2:=xlAscending
    End If
Set r = Nothing
    
End Sub

Hello Mr. John,

Thanks for your help.

Let me know if i can use something like:

'Seleciona a primeira linha visivel por baixo do cabeçalho
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

To select the first visible row, instead of:

Range(Cells(7, 1) ...

Another thing i don't understand is:

 Range(Cells(r.row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.row, "X"), Order1:=xlAscending ', Key2:=Cells(r.row, "B"), Order2:=xlAscending

Why is "A" And "B" , Is it correct?

The 3 in this case means Column "C", if you want to apply it too the used range use this instead.

Cells(x, activesheet.usedrange.columns.count)

My mistake:

In your sample the Header info ends on row 6 and the data begins on row 7.

Therefore Range(cells(7,1....

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("N7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(1).Find("NO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(r.row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.row, "X"), Order1:=xlAscending 
    End If
Set r = Nothing
    
End Sub

Key2 is commented out, but here I've removed it. It was a mistake. r.row is the row where "NO" begins after the sort. Did you test the code?

Originally Posted by JOHN H. DAVIS

My mistake:

In your sample the Header info ends on row 6 and the data begins on row 7.

Therefore Range(cells(7,1....

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("N7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(1).Find("NO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(r.row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.row, "X"), Order1:=xlAscending 
    End If
Set r = Nothing
    
End Sub

Key2 is commented out, but here I've removed it. It was a mistake. r.row is the row where "NO" begins after the sort. Did you test the code?

Hello Mr. John,

I've tried your code but it isn't working.

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).Row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("N7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(1).Find("NO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(r.Row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.Row, "X"), Order1:=xlAscending
    End If
Set r = Nothing
    
End Sub

You can check in my attached file, run brainzlp macro. See that when "SIM" is there, the measures (X column) DOESN'T sort.

@Richard Buttrey


Noted it. Thanks for information.

Check file in attach.

You have there more that, and check that when brand is "NÃO" the measures are not sorted out.

You have the macro inside the workbook as well

Column "X" Works for me.

Originally Posted by JOHN H. DAVIS

Column "X" Works for me.

I'm sorry but for me it doesn't work.

It works when brand is "NÃO" although when it is "YES" the X column is not sorted.

Can you please check it?

In the "YES" brand, the X column must be 60 then 70..

Am i misreading the map?

@JOHN H. DAVIS

Works perfectly, code was incorrect for my fault.

I don't want to sort N column but P column.

It's solved.

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).Row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("P7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(1).Find("NÃO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(r.Row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.Row, "X"), Order1:=xlAscending
    End If

Set r = Nothing
    
End Sub

Although i tried with more data, and inside "NÃO" it's not giving priority to MEasures, it's sorting by name as well..

I already made some attempts but can't put this working.

My goal really is:

1st need to Sort by Yes then NO in M Column (Marca)

Within Yes -> Sort P Column (Nome Flexo) -> then Sort X column (Largura)
Within No -> Sort X column (Largura) Only

Any information

Does "SIM" mean "YES" ? If so then try:

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).Row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("N7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(13).Find("NÃO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(7, 1), Cells(r.Row - 1, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("X" & r.Row - 1), Order1:=xlAscending
    Range(Cells(r.Row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.Row, "X"), Order1:=xlAscending
    End If
Set r = Nothing

End Sub

Yes, Sim = Yes, i will try as soon as i have the computer with me, 10 min and i get back again to you with feedback

HI, i've already tested, for the "NÃO" it's fine now.

Although in YES, it's not sorting firstly for name.

As you can see after running macro, the names are: tapasol r3d, centro centro , tapasol R3D.

They must be gathered.

Hope you can help with this

Mr. JOHN,

Thanks for your help, with your code i managed to put this working as needed.

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).Row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("P7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(13).Find("NÃO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(7, 1), Cells(r.Row - 1, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("P" & r.Row - 1), Order1:=xlAscending, Key2:=Range("X" & r.Row - 1), Order1:=xlAscending
    Range(Cells(r.Row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.Row, "X"), Order1:=xlAscending
    End If
Set r = Nothing

    
End Sub

Just some questions so i can understand and improve.

End(3) = end(Xlup) ?
Columns(13) , 13 means the number of the column in "brand" (Marca) correct?

r.row - 1 , why do we have to take 1 to the ROw number?

End(3) - Yes (xlUp)
No Columns(13) - means Columns(M) - The 13th Column of the spreadsheet
r.row -1 - It offsets -1 from the first cell with "NAO" and sorts on the range above it.

Originally Posted by JOHN H. DAVIS

End(3) - Yes (xlUp)
No Columns(13) - means Columns(M) - The 13th Column of the spreadsheet
r.row -1 - It offsets -1 from the first cell with "NAO" and sorts on the range above it.

I already tried and seems to work if i have "SIM" and "NO" or just "SIM"

Although if all column is with "NO" which can occur once in a while, it gives an error.

Try:

Sub brainzlp()
Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).Row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("P7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(13).Find("SIM", LookIn:=xlValues, lookat:=xlWhole)
    If r Is Nothing Then
    GoTo zz
    Else
    Set r = Nothing
Set r = Columns(13).Find("NÃO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(7, 1), Cells(r.Row - 1, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("P" & r.Row - 1), Order1:=xlAscending, Key2:=Range("X" & r.Row - 1), Order1:=xlAscending
    Range(Cells(r.Row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.Row, "X"), Order1:=xlAscending
    End If
Set r = Nothing
End If
zz:

    
End Sub

Hello Mr. John,

I've tried but it doesn't sort nothing any "SIM" values.

I'll opt for this, although it's

On Error Resume Next

Dim i As Long, r As Range, x As Long
x = Range("A" & Rows.Count).End(3).Row
Range(Cells(7, 1), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("M7"), Order1:=xlDescending, Key2:=Range("P7"), Order2:=xlAscending, Key3:=Range("X7"), Order3:=xlAscending
Set r = Columns(13).Find("NÃO", LookIn:=xlValues, lookat:=xlWhole)
    If Not r Is Nothing Then
    Range(Cells(7, 1), Cells(r.Row - 1, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Range("P" & r.Row - 1), Order1:=xlAscending, Key2:=Range("X" & r.Row - 1), Order1:=xlAscending
    Range(Cells(r.Row, "A"), Cells(x, ActiveSheet.UsedRange.Columns.Count)).Sort Key1:=Cells(r.Row, "X"), Order1:=xlAscending
    End If
Set r = Nothing
On Error GoTo 0