Sort range based on value

[lockarde]

Good morning all,

I have an issue with a spreadsheet I use to track job details. Ideally, these details are supposed to be entered as they come in, but sometimes people wait, and suddenly there are details from several days or even weeks past. Is there a way to sort all of these details by the request date? I know native sorting exists, but I have merged cells so sorting isn't possible. I'm not really sure how to store each job range during the sorting? My spreadsheet void of sensitive information is below:

Daily Engineering Reporting - TESTING.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Crate Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
2	Item 1	Type 1									4/7/2020	4/7/2020
3	Request Details:	Typical job contains 3 rows of information, column count never changes
4
5	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Crate Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
6	Item 1	Type 2									4/1/2020	4/3/2020
7	Request Details:
8
9	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Crate Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
10	Item 1	Type 3									4/1/2020	4/6/2020
11	Request Details:
12
13	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Crate Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
14	Item 1	Type 2									4/3/2020	4/7/2020
15	Request Details:	Rabble rabble details details
16	Notes:	Sometimes jobs contain 4 rows, with notes
17
18	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Spec	Mil Spec?	Prod Job?	Concept?	Design/BOM?
19	Item 1	Type 4									4/2/2020	4/3/2020
20	Request Details:
Sheet1

This is what I'm going for, each job range is sorted by the request date:

Daily Engineering Reporting - TESTING.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
25	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Crate Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
26	Item 1	Type 2									4/1/2020	4/3/2020
27	Request Details:
28
29	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Crate Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
30	Item 1	Type 3									4/1/2020	4/6/2020
31	Request Details:
32
33	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Spec	Mil Spec?	Prod Job?	Concept?	Design/BOM?
34	Item 1	Type 4									4/2/2020	4/3/2020
35	Request Details:
36
37	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Crate Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
38	Item 1	Type 2									4/3/2020	4/7/2020
39	Request Details:	Rabble rabble details details
40	Notes:	Sometimes jobs contain 4 rows, with notes
41
42	Item(s)	Request Type	Location	Employee	Job ID	Company	Job Value	Job Cost	Est Hours	Act Hours	Req Date	Comp Date	L	W	H	Qty.	Crate Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
43	Item 1	Type 1									4/7/2020	4/7/2020
44	Request Details:	Typical job contains 3 rows of information, column count never changes
Sheet1

How the jobs are sorted if they have the same request date isn't important, that can be any order.
I know this is a bit of a bear, so any help is greatly appreciated!

 

[lockarde]

I finally figured this out. I haven't completely applied it to my actual tracking spreadsheet, there will be some tweeks, but I got it working great in my test sheet. What I ended up doing is searching through the spreadsheet and putting dates, and the address from .Find into a 2D array. Then I sorted that array, and as it goes through and sorts, once a value is at its "final" location in the array, it copies the rows in question to a 'temp' sheet. Once all the rows are in the temp sheet, in the correct order, it copies everything over into the tracking sheet. My code is below:

Sub DateSort()
Dim actsheet As Worksheet, rqdate As String, job As Range
Dim Rdate As Range, dates() As String, t As Integer, y As Integer
Dim r As Integer, actname As String, itemfind As Range, temp As String
'Dim jobadd As Address
Application.ScreenUpdating = False
Set actsheet = ActiveSheet
actname = actsheet.Name
Sheets("Temp").UsedRange.Delete
    With Sheets(actname)
        ReDim dates(.Cells(1, 8).Value - 1, 1) As String
        Set Rdate = .Cells.Find("Req Date")
        Set job = Intersect(Range(Rdate.Offset(0, -9), .Cells(Rdate.Row + 2, _
            Rdate.Column + 10)), Range("A:U"))
        rqdate = Rdate.Offset(1, 0).Value
        firsjob = Rdate.Address
        r = 0

        Do
             
            rqdate = Rdate.Offset(1, 0).Value
            Set job = Intersect(Range(Rdate.Offset(0, -9), Rdate.Offset(0, -9). _
                End(xlDown).EntireRow), Range("A:T"))
            If r = 0 Then
                dates(0, 0) = rqdate
                dates(0, 1) = Rdate.Address
                r = r + 1
            Else
                dates(r, 0) = rqdate
                dates(r, 1) = Rdate.Address
                r = r + 1
            End If

            Set Rdate = .Cells.FindNext(Rdate)
            
        Loop While Rdate.Address <> firsjob
Application.StatusBar = "Sorting jobs....."
    For t = 0 To r Step 1
        For y = t + 1 To r - 1 Step 1
            If day(dates(y, 0)) < day(dates(t, 0)) Then
                With Sheets(actname)
                    temp = dates(t, 0)
                    tempadd = dates(t, 1)
                    dates(t, 0) = dates(y, 0)
                    dates(t, 1) = dates(y, 1)
                    dates(y, 0) = temp
                    dates(y, 1) = tempadd
                End With
            End If
        Next y
        With Sheets(actname)
            If y <= 23 Then
                lastrow = Sheets("Temp").Cells(Sheets("Temp").Rows.Count, "A").End(xlUp).Row
                Set job = Intersect(Range(.Range(dates(t, 1)).Offset(0, -9), .Range(dates(t, 1)).Offset(0, -9). _
                    End(xlDown).EntireRow), .Range("A:T"))
                job.Copy: Sheets("Temp").Cells(lastrow + 2, 1).PasteSpecial Paste:=8, Operation:=xlNone
                Sheets("Temp").Cells(lastrow + 2, 1).PasteSpecial Paste:=-4104, Operation:=xlNone
            End If
        End With
    Next t

    End With
    Sheets(actname).UsedRange.Offset(3, 0).Delete
    Sheets("Temp").UsedRange.Copy: Sheets(actname).Cells(4, 1).PasteSpecial Paste:=8, Operation:=xlNone
    Sheets(actname).Cells(4, 1).PasteSpecial Paste:=-4104, Operation:=xlNone
    Application.CutCopyMode = False
    Sheets(actname).Range("H1").Formula = "=CountIf(A:A, ""Item(s)"")"
    Sheets(actname).Range("M2").Formula = "=SUM(I4:I250) & "" / "" & SUM(H4:H250)"
Application.StatusBar = "Sorting Complete."
Application.StatusBar = ""
Sheets(actname).Range("A1").Select
End Sub