Sort data A-Z

[ngocanh87]

Hello all,

I want sort by M4 & D4 to A-Z as (1) but how can get DLVS on top then DLV (2). Thank all

Sub SCN001()
Range("A4:AZ5").AutoFilter Field:=8, Criteria1:=""
Range("A4:AZ500").Sort Key1:=Range("M4"), Key2:=Range("D4"), Header:=xlYes, Order1:=xlAscending, Order2:=xlAscending End Sub

(1)

SCN001(Delivery).xls
	D									M
4	Mã phiếu giao vận									Khách hàng
5	DLV-20230329-031									C00039
6	DLV-20230329-032									C00039
7	DLV-20230329-032									C00039
8	DLV-20230329-032									C00039
9	DLV-20230329-052									C00144
10	DLV-20230329-052									C00144
11	DLV-20230329-053									C00144
12	DLV-20230329-038									C00144-01
13	DLV-20230329-039									C00144-01
14	DLV-20230329-040									C00144-01
15	DLV-20230329-041									C00144-01
16	DLV-20230329-041									C00144-01
17	DLV-20230329-042									C00144-01
18	DLV-20230329-043									C00144-01
19	DLV-20230329-044									C00144-01
20	DLV-20230329-045									C00144-01
21	DLV-20230329-046									C00144-01
22	DLV-20230329-047									C00144-01
23	DLV-20230329-048									C00144-01
24	DLV-20230329-034									C00337
25	DLV-20230329-034									C00337
26	DLV-20230329-034									C00337
27	DLV-20230329-034									C00337
28	DLV-20230329-035									C00337
29	DLV-20230329-035									C00337
30	DLV-20230329-035									C00337
31	DLV-20230329-035									C00337
32	DLV-20230329-055									C00388
33	DLV-20230329-056									C00388
34	DLV-20230329-056									C00388
35	DLV-20230329-057									C00388
36	DLV-20230329-058									C00388
37	DLV-20230329-058									C00388
38	DLV-20230329-059									C00388
39	DLV-20230329-060									C00388
40	DLV-20230329-061									C00388
41	DLV-20230329-061									C00388
42	DLV-20230329-062									C00388
43	DLV-20230329-063									C00388
44	DLV-20230329-064									C00388
45	DLV-20230329-065									C00388
46	DLV-20230329-066									C00388
47	DLV-20230329-067									C00388
48	DLV-20230329-068									C00388
49	DLVS-20230329-004									C00392
50	DLVS-20230329-006									C00392
51	DLV-20230329-033									C00677
52	DLV-20230329-069									C00846
53	DLV-20230329-069									C00846
54	DLV-20230329-069									C00846
55	DLV-20230329-069									C00846
56	DLV-20230329-069									C00846
57	DLV-20230329-069									C00846
58	DLV-20230329-069									C00846
59	DLV-20230329-069									C00846
60	DLV-20230329-069									C00846
61	DLV-20230329-070									C00846
62	DLV-20230329-070									C00846
63	DLV-20230329-070									C00846
64	DLV-20230329-070									C00846
65	DLV-20230329-070									C00846
66	DLV-20230329-070									C00846
67	DLV-20230329-070									C00846
68	DLV-20230329-070									C00846
69	DLV-20230329-070									C00846
70	DLV-20230329-070									C00846
71	DLV-20230329-070									C00846
72	DLVS-20230329-002									C01021-01
73	DLVS-20230329-003									C01021-01
74	DLV-20230329-026									C01065
75	DLV-20230329-026									C01065
76	DLVS-20230329-001									C01213
77	DLV-20230329-018									C01272
78	DLV-20230329-019									C01272
79	DLV-20230329-036									C01289
80	DLVS-20230329-005									C01302
SCN001

(2)

SCN001(Delivery).xls
	D	E
2	Mã phiếu giao vận	Khách hàng
3	DLVS-20230329-004	C00392
4	DLVS-20230329-006	C00392
5	DLVS-20230329-002	C01021-01
6	DLVS-20230329-003	C01021-01
7	DLVS-20230329-001	C01213
8	DLVS-20230329-005	C01302
9	DLV-20230329-031	C00039
10	DLV-20230329-032	C00039
11	DLV-20230329-032	C00039
12	DLV-20230329-032	C00039
13	DLV-20230329-052	C00144
14	DLV-20230329-052	C00144
15	DLV-20230329-053	C00144
16	DLV-20230329-038	C00144-01
17	DLV-20230329-039	C00144-01
18	DLV-20230329-040	C00144-01
19	DLV-20230329-041	C00144-01
20	DLV-20230329-041	C00144-01
21	DLV-20230329-042	C00144-01
22	DLV-20230329-043	C00144-01
23	DLV-20230329-044	C00144-01
24	DLV-20230329-045	C00144-01
25	DLV-20230329-046	C00144-01
26	DLV-20230329-047	C00144-01
27	DLV-20230329-048	C00144-01
28	DLV-20230329-034	C00337
29	DLV-20230329-034	C00337
30	DLV-20230329-034	C00337
31	DLV-20230329-034	C00337
32	DLV-20230329-035	C00337
33	DLV-20230329-035	C00337
34	DLV-20230329-035	C00337
35	DLV-20230329-035	C00337
36	DLV-20230329-055	C00388
37	DLV-20230329-056	C00388
38	DLV-20230329-056	C00388
39	DLV-20230329-057	C00388
40	DLV-20230329-058	C00388
41	DLV-20230329-058	C00388
42	DLV-20230329-059	C00388
43	DLV-20230329-060	C00388
44	DLV-20230329-061	C00388
45	DLV-20230329-061	C00388
46	DLV-20230329-062	C00388
47	DLV-20230329-063	C00388
48	DLV-20230329-064	C00388
49	DLV-20230329-065	C00388
50	DLV-20230329-066	C00388
51	DLV-20230329-067	C00388
52	DLV-20230329-068	C00388
53	DLV-20230329-033	C00677
54	DLV-20230329-069	C00846
55	DLV-20230329-069	C00846
56	DLV-20230329-069	C00846
57	DLV-20230329-069	C00846
58	DLV-20230329-069	C00846
59	DLV-20230329-069	C00846
60	DLV-20230329-069	C00846
61	DLV-20230329-069	C00846
62	DLV-20230329-069	C00846
63	DLV-20230329-070	C00846
64	DLV-20230329-070	C00846
65	DLV-20230329-070	C00846
66	DLV-20230329-070	C00846
67	DLV-20230329-070	C00846
68	DLV-20230329-070	C00846
69	DLV-20230329-070	C00846
70	DLV-20230329-070	C00846
71	DLV-20230329-070	C00846
72	DLV-20230329-070	C00846
73	DLV-20230329-070	C00846
74	DLV-20230329-026	C01065
75	DLV-20230329-026	C01065
76	DLV-20230329-018	C01272
77	DLV-20230329-019	C01272
78	DLV-20230329-036	C01289
Sheet1

 

[Joe4]

Perhaps pull off the prefix into a helper column like this:

=LEFT(D5,FIND("-",D5)-1)

and use that in your primary sort, and then your other two the second and third sort fields.

 

[ngocanh87]

Hi, thanks

Sub SCN001()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 4).End(xlUp).Row

Range("A4:AZ5").AutoFilter Field:=8, Criteria1:=""
Range("E5:E" & lastrow).NumberFormat = "General"
Range("E5:E" & lastrow).Formula = "=LEFT(D5,FIND(""-"",D5)-1)"

Range("A4:AZ500").Sort Key1:=Range("E4"), Key2:=Range("D4"), Key3:=Range("M4"), Header:=xlYes, Order1:=xlDescending, Order2:=xlAscending, Order3:=xlAscending

End Sub

 

[rlv01]

If you are using VBA and don't want to use a helper column, another possibility is to use the range replace method.

Sub SCN002()
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, 4).End(xlUp).Row

    Range("A4:AZ5").AutoFilter Field:=8, Criteria1:=""
    Range("D5:D" & lastrow).Replace What:="DLVS", Replacement:="ALVS", LookAt:=xlPart
    Range("A4:AZ500").Sort Key1:=Range("D4"), Key2:=Range("M4"), Header:=xlYes, Order1:=xlAscending, Order2:=xlAscending
    Range("D5:D" & lastrow).Replace What:="ALVS", Replacement:="DLVS", LookAt:=xlPart
End Sub

 

[ngocanh87]

Thank you, but M4 Customer code not to A-Z (smallest-biggest).

 

[bebo021999]

One way with helper column:

try to combine "A" (or "Z") with text in column M:

=IF(SEARCH("-",D5)=5,"A","Z")&M5

or

=IF(MID(D5,4,1)="S","A","Z")&M5

then filter this column.

(P/S: Nếu muốn dùng tiếng Việt thì vô đây nhé www.giaiphapexcell.com, nick của mình cũng vậy)

 

[rlv01]

Thank you, but M4 Customer code not to A-Z (smallest-biggest).

Sub SCN002()
    Dim WS As Worksheet
    Dim ColRange As Range

    Set WS = ActiveSheet
    Set ColRange = WS.Range("D5", WS.Range("D" & WS.Rows.Count).End(xlUp))

    WS.AutoFilterMode = False
    ColRange.Replace What:="DLVS", Replacement:="ALVS", LookAt:=xlPart
    With WS.Range("A4:AZ500")
        .Sort Key1:=Range("D4"), Key2:=Range("M4"), Header:=xlYes, Order1:=xlAscending, Order2:=xlAscending
        .AutoFilter Field:=4, Criteria1:="=ALVS*"
        With Application.Intersect(WS.UsedRange, .SpecialCells(xlCellTypeVisible))
            .Sort Key1:=Range("M4"), Key2:=Range("D4"), Header:=xlYes, Order1:=xlAscending, Order2:=xlAscending
        End With
        .AutoFilter Field:=4, Criteria1:="=DLV-*"
        With Application.Intersect(WS.UsedRange, .SpecialCells(xlCellTypeVisible), .Offset(1))
            .Sort Key1:=Range("M4"), Key2:=Range("D4"), Header:=xlNo, Order1:=xlAscending, Order2:=xlAscending
        End With
    End With
    WS.AutoFilter.ShowAllData
    ColRange.Replace What:="ALVS", Replacement:="DLVS", LookAt:=xlPart
End Sub