Multiple Level Data Sorting in Excel - 1st Level Date (Oldest to Newest) & 2nd Level Age > 11 if not sorting sorting

[p45cal]

Another issue I found with the code is the Row Number changes when one of the loops is executed.

You're beginning to appreciate how you have to think of everything when coding; just when you think you've got the algorithm right it throws another gotcha at you!

I've been doing some background reading about sugarcane production! Interesting stuff.
To understand the context of this thread, am I right in thinking the Daily Quota sheet is harvest tonnage from the fields on those dates?
And that that harvest has to be taken to the mill that day (or asap)?
Are you at the mill, a producer of raw cane, both? Where do you stand in the whole process?
What is the processing each row represents?
On the Sort Formula sheet what do the Names represent? Are they perhaps fields? Perhaps they're company orders?
Anything you can add (because it brings to life the problem I'm helping to solve) would be great.

What country is all this happening in?

I see there are a few rows with long periods between Grow Start and Not Before date, some approaching 2 years: CW, CX, CZ, CY. Are these correct?

I'll look at his again tomorrow, it's late evening here.

 

[HansrajR]

Daily Quota is the maximum number of tons of sugarcane which can be sent to the mill from the fields on the corresponding date.

Sugarcane from Harvested fields are sent to the mill on the same day, else the sugarcane quality decreases.

As a side note, you will notice in the Daily Quota table, the number of tons is zero for Sundays and Public Holidays which accounts for Mill shutdown for Sundays and Public Holidays. This is the only allowable break (Sundays and Public Hoildays) in fields harvesting, and therefore for remaining days the harvesting of fields should continue for non-stop crushing at the mill.

Variety of Sugarcane cane differ in the fields. The Not Before and Not After dates are the optimal time period for Harvest for each variety. The parameter of Age (Difference between Harvest Date and Grow Start date) greater than 11 month is applied because sugarcane with lower than 11 have lower sugar content than sugarcane with age greater than 11 months.

Myself, I am a Harvest Planner (I am based in Mauritius) to schedule the Harvest Date of each sugarcane field based on the

1. Daily Quota (provided by the mill) for each day of the Cutting season (15-June-2021 to 15-Dec-2021) - Available in the Daily Quota table
2. Age of sugarcane in the fields ( greater or equal to 11 months)
3. Optimal Harvest period of sugar cane variety in the fields (Not Before and Not After dates).

Sorting the fields (moving the rows down and up in the spreadsheet) is equivalent to postponing Harvest date of a field (moving down) or scheduling Harvest earlier (moving up).

The names on the Sort Formula Sheet are the Fields' identification numbers.

Kindly find attached original data I am working on with real world data.

 

[p45cal]

I see there are a few rows with long periods between Grow Start and Not Before date, some approaching 2 years: CW, CX, CZ, CY. Are these correct?

The new sheet you attached doesn't have such outliers. Thanks.

1. Daily Quota (provided by the mill) for each day of the Cutting season (15-June-2021 to 15-Dec-2021) - Available in the Daily Quota table
2. Age of sugarcane in the fields ( greater or equal to 11 months)
3. Optimal Harvest period of sugar cane variety in the fields (Not Before and Not After dates).

I need to get this clear; here you're suggesting the >11 months takes priority over harvesting being between the Not Before/Not After dates.
Elsewhere, I think you've implied the other way round. Can you guide me?
Note that in the new data, there are 7 batches where the Not After date is less than 11 months after the Grow Start date (A655, A667, A755, A837, A838, A842, A855). I grant that they're not far from 11 months (10.4 months the worst), but it's obvious that they're never going to fulfil all the requirements.

Your most recent post was very helpful indeed!
I'm going to move my trials to this new sheet and do some testing but be aware that in the next few days (until possibly next Tuesday/Wednesday) I'm going to have very little time.
At the moment, I'm thinking along the following lines: Since most of the time (77%), the 11 months point appears within the Not Before/After dates, I'm thinking I'll narrow the harvest window to the part only after 11 months, then I'll have a column which uses that to score how well a row could be the next row to process. I'm also considering increasing that score if the remaining harvest window is small so that those fields are more likely to be processed when they need to be processed. That column will become part of the sorting to bring the best row to the top.

 

[HansrajR]

The order of priority is first harvesting being between the Not Before and Not After dates and then age>= 11.

 

[HansrajR]

I have been trying the following code, adding counter to count the number of iterations (loops) to ensure that values in the same row are being compared in each iteration.

I am convinced a workflow as in the flow diagram will give good results as I verified it manually but I can't get the coding right

Sub test()

Dim rw As Long
Dim L As Integer
Dim M As Integer
Dim N As Integer

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To rw = LastRow

    .Cells(rw, 15).Select
    
            If Cells(rw, 15).Value < Cells(rw, 28).Value Then 'If Start date less than Not Before date
                Do Until Cells(rw + L, 15).Value >= Cells(rw + L, 28).Value 'Continue loop until Start date is greater or equal to Not Before date
                .Cells(rw + L, 15).EntireRow.Select
                Selection.Cut
                .Cells(rw + L + 2, 15).EntireRow.Select
                Selection.Insert Shift:=xlDown
          
                L = L + 1 'Count Loops
          
                Call Copy_formula_from_rowNumber3_paste_up_to_last_row
          
            Loop
          
            End If
      
        If Cells(rw, 31).Value < 11 Then 'If Age at Start date is smaller than 11
            Do Until Cells(rw + M, 31).Value >= 11 'Continue loop until Age at Start date is greater or equal to 11
                    .Cells(rw + M, 15).EntireRow.Select
                    Selection.Cut
                    .Cells(rw + M + 2, 15).EntireRow.Select
                    Selection.Insert Shift:=xlDown
          
                    M = M + 1 'Count Loops
          
                Call Copy_formula_from_rowNumber3_paste_up_to_last_row
          
            Loop
      
        End If
      
            If Cells(rw, 15).Value > Cells(rw, 29).Value Then 'If Start date greater than Not After date
                Do Until Cells(rw - N, 15).Value <= Cells(rw - N, 29).Value 'Continue loop until Start date is less or equal to Not After date
                    .Cells(rw - N, 15).EntireRow.Select
                    Selection.Cut
                    Selection.Cut
                    Selection.Offset(rw - N - 2).Insert
          
                    N = N + 1 'Count Loops
          
                Call Copy_formula_from_rowNumber3_paste_up_to_last_row
          
                Loop
          
            End If
      
Next rw
End With

    'Variable Declaration
    Dim OutPut As Integer

    'Example of vbInformation
    OutPut = MsgBox("Succesessfully Completed", vbInformation, "Sequence Optimizer")

 

[p45cal]

I'll revisit this in the middle of next week - my time is not my own until then.

 

[HansrajR]

I got to the below code using the principle illustrated by the following image but it needs further refinement.

*I have re-attached the sample data. In the previous attachment, the VBA macro I used to copy formulae from Row Number 3 and paste in rows below after each iteration, copied also the Not Before and Not after dates.

Not worry about empty cells, it's just to remove confidential data and does not affect the process.

Kindly let me have your views when you are free.

Sub HarvestSequenceOptimizer()

Dim rw As Long
Dim L As Integer
Dim M As Integer
Dim N As Integer

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To LastRow
 
    .Cells(rw, 15).Select 'Select Cell in row with Start date "O"
      
        If .Cells(rw, 15) < .Cells(rw, 28) And .Cells(rw + 1, 15) >= .Cells(rw + 1, 28) Then 'If, in selected row, Start "O" < Not Before "AB" and in next row Start "O" >= Not Before "AB"
            
            Do Until .Cells(rw, 15) >= .Cells(rw, 28) 'Until, in selected row Start "O" >= Not Before "AB"
                
                .Cells(rw, 15).EntireRow.Select 'Select row
                Selection.Cut 'Cut row
                .Cells(rw + 2, 15).EntireRow.Select 'Select 2 rows down
                Selection.Insert Shift:=xlDown 'Insert previously cut row
                    
            Loop
            
            Call ComputeStartCutDateandHarvestAge 'Re-calculate Start date and Age as from Row Number 4 by copying formula in Row Number 3 to Row Number 4 upto last row
            
                    ElseIf .Cells(rw, 15) < .Cells(rw, 28) And .Cells(rw + 1, 15) < .Cells(rw + 1, 28) Then 'If, in both selected row and next row, Start "O" < Not Before "AB"
                    
                        Do Until .Cells(rw + L + 1, 15) >= .Cells(rw + L + 1, 28) 'Until, Start "O" >= Not Before "AB" in row after loop
                            
                            Rows(rw & ":" & rw + L).Select 'Select all rows until Start "O" >= Not Before "AB" in row after loop
                            Selection.Cut 'Cut all rows in loop
                            .Cells(rw + L + 2, 15).EntireRow.Select 'Ínsert all selected rows under the row whereby Start Date "O" >= Not Before "AB" after loop
                            Selection.Insert Shift:=xlDown
                        
                            L = L + 1 'Count Number of Loops
                            
                        Loop
                    
                        Call ComputeStartCutDateandHarvestAge 'Re-calculate Start date and Age as from Row Number 4 by copying formula in Row Number 3 to Row Number 4 upto last row
    
        End If
        
        If .Cells(rw, 22) < 11 And .Cells(rw + 1, 22) >= 11 Then 'If, in selected row, Age "V" < 11 and in next row Age "V" >= 11
        
            Do Until .Cells(rw, 22) >= 11 'Ín selected row Age "V" >= 11
        
                .Cells(rw, 22).EntireRow.Select 'Select entire row
                Selection.Cut 'Cut row
                .Cells(rw + 2, 22).EntireRow.Select  'Select 2 rows down
                Selection.Insert Shift:=xlDown 'Insert previously cut row
                    
            Loop
            
            Call ComputeStartCutDateandHarvestAge 'Re-calculate Start date and Age as from Row Number 4 by copying formula in Row Number 3 to Row Number 4 upto last row
            
                ElseIf .Cells(rw, 22) < 11 And .Cells(rw + 1, 22) < 11 Then 'If, in both selected row and next row Age "V" < 11
                    
                    Do Until .Cells(rw + M + 1, 22) >= 11 'Until, Age "V" >= 11 in row after loop
                    
                        Rows(rw & ":" & rw + M).Select 'Select all rows until Age "V" >= 11 in row after loop
                        Selection.Cut 'Cut all rows in loop
                        .Cells(rw + M + 2, 22).EntireRow.Select 'Ínsert all selected rows under the row whereby Age "V" >= 11 after loop
                        Selection.Insert Shift:=xlDown
                    
                        M = M + 1 'Count Number of Loops
                        
                    Loop
                    
                    Call ComputeStartCutDateandHarvestAge 'Re-calculate Start date and Age as from Row Number 4 by copying formula in Row Number 3 to Row Number 4 upto last row
            
        End If
        
        If .Cells(rw, 15) > .Cells(rw, 29) And .Cells(rw + 1, 15) <= .Cells(rw + 1, 29) Then 'If, in selected row, Start "O" > Not After "AC" and in next row Start "F" <= Not After "P"
            
            Do Until .Cells(rw, 15) <= .Cells(rw, 29) 'Until, in selected row Start "O" <= Not After "AC"
                
                .Cells(rw, 5).EntireRow.Select 'Select entire row
                Selection.Cut 'Cut row
                .Cells(rw - 1, 5).EntireRow.Select 'Select 2 rows above
                Selection.Insert Shift:=xlDown 'Insert previously cut row 2 rows above
                    
            Loop
            
            Call ComputeStartCutDateandHarvestAge 'Re-calculate Start date and Age as from Row Number 4 by copying formula in Row Number 3 to Row Number 4 upto last row
            
                ElseIf .Cells(rw, 15) < .Cells(rw, 29) And .Cells(rw + 1, 15) < .Cells(rw + 1, 29) Then 'If, in both selected row and next row Start "O" > Not After "AC"
                
                    Do Until .Cells(rw + N + 1, 15) <= .Cells(rw + N + 1, 29) 'Until, Start "O" <= Not After "AC" in row after loop
                        
                        Rows(rw & ":" & rw + L).Select 'Select all rows until Start "O" <= Not After "AC" in row after loop
                        Selection.Cut 'Cut all rows in loop
                        .Cells(rw - 1, 15).EntireRow.Select 'Select 2 rows above
                        Selection.Insert Shift:=xlDown 'Insert previously cut row 2 rows above
                    
                        N = N + 1 'Count Number of Loops
                    
                    Loop
                    
            Call ComputeStartCutDateandHarvestAge 'Re-calculate Start date and Age as from Row Number 4 by copying formula in Row Number 3 to Row Number 4 upto last row
        
        End If
      
    Next rw

Dim OutPut As Integer

OutPut = MsgBox("The processing is complete.", vbInformation, "Harvest Sequence Optimizer")

End With

End Sub

 

[HansrajR]

Obtained some results with the attached code, but need further testing.