SORTING MERGED CELLS BY DATE WITHOUT SEPARATING THE DATA AND CELLS." Eğer başka bir ko

[suleymantaha]

Order No.; This line will remain fixed, and names will be included in the ranking according to their lines.
Duty Location; This line will not change.
Duty Departure; The ranking will be based on this line.
Duty Return; This line will not change.
Names; Names represent personnel who have a sequence number and have been on duty on that date.
Process to be done; I get data from two different lists like this, and when I paste them one under the other, I can't sort because there's a merged cell. I want to sort by the duty departure date without changing its content.

 

[Joe4]

Welcome to the Board!

This is a big problem with merged cells - using them takes away a lot of abilities, such as the ability to effectively sort, use certain VBA codes, and other features.
Merged cells cause a TON of problems with Excel, and as such, it is HIGHLY recommended that you avoid using them (most experienced programmers won't touch them with a 10 foot pole!).

Without removing them or altering your cells, I don't know that you are going to be able to do what you want.

 

[suleymantaha]

"Thank you very much. I know, but that's how they operate within the institution. We also send it in that format, so we can't split the cells. I haven't tried it with Python; I've tried many ways, but it doesn't work. I haven't tried exporting as a PDF either, but I'll look into that next. I appreciate the time you took to write to me.

 

[Peter_SSs]

@suleymantaha
Please also review the Forum Rules, particularly #14 regarding the use of upper case in thread titles. Thanks.

 

[Alex Blakenburg]

If you are open to using a macro you could give this a try.
I am assuming:-
• Sorting by Column D which is a date field
• Column F is not in use (I am temporarily replicating the Date field there)

Sub SortMergedData()

    Dim ws As Worksheet
    Dim rngMerged As Range, rngDetail As Range
    Dim arrMerged As Variant, arrDetail As Variant
    Dim lngDetDt As Long
    Dim rowLast As Long, i As Long
    
    Application.ScreenUpdating = False
    
    Set ws = ActiveSheet
    rowLast = ws.Range("E" & Rows.Count).End(xlUp).Row
    Set rngDetail = ws.Range("E2:E" & rowLast)
    arrDetail = rngDetail.Resize(, 2)
    Set rngMerged = ws.Range("A2:D" & rowLast)
    arrMerged = rngMerged.Value2
    
    For i = 1 To UBound(arrDetail)
        If arrMerged(i, 4) <> "" Then
            lngDetDt = arrMerged(i, 4)
        End If
        arrDetail(i, 2) = lngDetDt
    Next i
    
    rngDetail.Offset(, 1) = Application.Index(arrDetail, 0, 2)
    
    rngMerged.Sort Key1:=Range("D2"), _
                     Order1:=xlAscending, _
                     Header:=xlNo
    rngDetail.Resize(, 2).Sort Key1:=Range("F2"), _
                     Order1:=xlAscending, _
                     Header:=xlNo
                     
    rngDetail.Offset(, 1).ClearContents
    
    Application.ScreenUpdating = True
                     
                     
End Sub

My test data in case anyone else want to have a go was:

20230905 VBA Sort Merged Cells suleymantaha.xlsm
	A	B	C	D	E
1	Order No	Duty Location	Duty Departure	Duty Return	Names
2	3	Loc3	Dept 3	5/09/2023	Name 3 1
3					Name 3 2
4					Name 3 3
5					Name 3 4
6					Name 3 5
7	2	Loc2	Dept 2	20/08/2023	Name 2 1
8					Name 2 2
9					Name 2 3
10					Name 2 4
11					Name 2 5
12	1	Loc1	Dept 1	15/08/2023	Name 1 1
13					Name 1 2
14					Name 1 3
15					Name 1 4
16					Name 1 5
Data