• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

HansrajR

Member
I need to sort the rows as from row number 4 to the end of the table based on:

1st Level Sorting: Processing Not Before (Column K) - Oldest to Newest i.e. as from 01-June-2021.

2nd Level Sorting: A condition is achieved i.e. Age at Processing Start Date (Column M) is greater than 11.

Value in Column M is dependent on the row number. When the row number changes, the corresponding Age at Processing Start changes.

Therefore, Excel must continue sorting until the best achievable sequence is reached.

Please find attached the spreadsheet.

74671
 

Attachments

vletm

Excel Ninja
HansrajR
How user will know, when the best achievable sequence is reached?
As this thread is
Ask an Excel Question,
then user could sort M-column manually as many times as ... the best achievable sequence is reached.
 

p45cal

Well-Known Member
What you seem to be wanting is repeatedly to sort the table, taking the top n rows and accepting that, then sorting the rows below that, taking the next m rows, then sorting below that again and taking the next p rows etc. util you get to the bottom of the list?
It looks like the answer might involve starting at the top with whatever you choose as the first row to process, then looking at the remaining and choosing which row among them to take next, process that row etc. etc (ie one row at a time being chosen from the remaining rows).

I don't understand the priorities and aspects that you take into account to determine the 'best' order so I'm asking you to take the attached file where in the Random sheet I've taken your rows only of raw data (not calculated) (deliberately in no particular order) and I want you to tell me in words:
1. which one of the rows you'd choose to process the very first and why.
(Let's say you drag that row out of the list and put it at the top of another list.)
2. After that, bearing in mind values on the Daily Quota sheet, priorites and aspects of the remaining rows, tell me, in words, not formulae, how you would go about choosing the next row to process, which one you would choose and why.
3. Same for the 3rd row
4. etc.

oops, forgot to attach, here it is:
 

Attachments

Last edited:

HansrajR

Member
Thanks for your interest in the problem statement.

The process described in your response is the result I am seeking.

The process must start from Row Number 4, because in:

1. Row Number 2 (1st Row below Header) - User inputs the Processing Start Date (unlike in the following rows the Processing Start Date is calculated as a function of Processing Start Date and Processing End Date of preceding row).

2. Row Number 3 (3rd Row below Header) - Contains the formula to calculate Processing Start Date as a function of Processing Start Date in Row Number 2. Cell references in Row Number 3 point to cells in Row Number 2 whereas formula for all following cells have cell references pointing to preceding row.

Therefore, moving Row Number 3 will give reference error when sorting is started.

Logic behind Calculations

To calculate the Processing End Date from Processing Start Date, the formula looks for Processing Start Date in the Daily Quota sheet, starts adding the Daily Tonnage as from that date until the specified Tonnage is reached. The number of rows is counted and this number is added to the Processing Start Date to calculate the Processing End Date.

The Tonnage processed on the last day is calculated to verify the fraction of the day used.

On event, less than one full day is used, processing of the next batch is started.

The Age is calculated from difference between Grow Start Date and computed Processing Start Date.

Sorting of rows.

The process of sorting is started from Row number 4 for reasons previously outlined.

The Processing Start Date must be between the Processing Not Before and Processing Not After dates, if yes the age is verified. (Age will be re-calculated by formula each time the row arrangement is changed).

On event the age is greater than 11, the row is not moved, else it is moved to the next row downwards until both conditions are met.

On event, the Processing Start Date is not between Processing Not Before and Processing Not After dates, the row is moved to the next row downwards until this condition is satisfied.

This process is continued and ends until the last row is reached.

It is important to note before any calculations were made, the data was sorted 1st by Processing Not Before date (Oldest to Newest) , then by Processing Not After date Processing Not Before and Processing Not After dates, then by Grow Start Date (as Age is calculated as a function of Grow Start Date)

Kindly let me know if more details is needed.
 

HansrajR

Member
The criteria to stop moving a particular row down the spreadsheet is when Processing Start Date exceeds Not After date.

With this criteria, the Processing Start Date will always be between Not Before and Not After date.

This criteria determines the best possible criteria.
 

p45cal

Well-Known Member
You've discovered for yourself that sorting the range (or part of the range) doesn't work well because as soon as you move rows, the data has to recalculate, and the column(s) you've sorted on are no longer sorted. This is because some of the column's calculations look at the row above.

I think a good solution is to pick the best row (most likely row) which fills the criteria by doing so one row at a time, which was the reason I asked the questions in my last message. I can work out some of the answers to my questions from your reply, but not all; how for instance, is the first line chosen?

Putting that to one side for the moment, would a macro be acceptable?
If so, there's one below which should be run while the Sort Formula sheet in the file you attached is the active sheet (or a copy of that sheet). It's not likely to work well on other sheets since there's a lot of hard-coded values.
It's only work-in-progress, which means it could be improved upon to get a better order - not all checks are carried out either; it's a start.
What it does is, starting at row 3 of the sheet (it won't do anything to that row) it changes the formulae of all the rows below that row, but only in columns D, E & H (because they're the only columns whose formulae look at the row above), to look at row 3 instead, so that all those rows become candidates for row 4. I then sort those rows (probably wrongly!) so that the top row of the sorted range is a good row to keep there. Then the code convert's that row's formulae back to what they were, and assumes that that row is acceptable.
It does all that again, only from row 4 downwards, then again from row 5 downwards etc.
It is slow (there are a lot of recalculations to be done - I could speed it up by removing calculations as we move down the list and just leave plain values).

Put the code into a standard code-module of the same workbook, make the sheet you want to process the active sheet, run the macro and be prepared to wait (5 minutes here).

This is not a solution I like and believe that I can get a better and faster answer by picking the rows in a different way - if you were to answer questions.

The code:
Code:
Sub blah()
Application.ScreenUpdating = False
For rw = 3 To 189  'rw IS THE LAST ACCEPTED ROW
'change all the formulae in columns D, E & H as if they were all in the row below the last accepted row:
  Range("D" & rw + 1 & ":D190").FormulaR1C1 = "=RC[-1]+R" & rw & "C[4]" 'NEW
  Range("E" & rw + 1 & ":E190").FormulaR1C1 = "=IF(R" & rw & "C[5]<1,R" & rw & "C[1],IF(AND(R" & rw & "C[5]>1,R" & rw & "C[5]=INT(R" & rw & "C[5])),R" & rw & "C[1]+1,R" & rw & "C[1]))" 'new
  Range("H" & rw + 1 & ":H190").FormulaR1C1 = "=IF(RC[-4]>=INDEX('Daily Quota'!R2C2:'Daily Quota'!R201C2,MATCH(RC[-3],'Daily Quota'!R2C1:'Daily Quota'!R201C1,0)),(RC[-4]-SUM(INDEX('Daily Quota'!R2C2:'Daily Quota'!R201C2,MATCH(RC[-3],'Daily Quota'!R2C1:'Daily Quota'!R201C1,0)):INDEX('Daily Quota'!R2C2:'Daily Quota'!R201C2,MATCH(RC[-2]-1,'Daily Quota'!R2C1:'Daily Quota'!R201C1,0)))),R" & rw & "C+RC[-5])" 'new

'here sort and keep top row:
'Range("A" & rw + 1 & ":M190").Select ' just shows you which rows will be sorted (no headers)

    With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range("K" & rw + 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add2 Key:=Range("L" & rw + 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add2 Key:=Range("B" & rw + 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SortFields.Add2 Key:=Range("M" & rw + 1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
      .SetRange Range("A" & rw + 1 & ":M190")
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With

'restore the formulae in columns D, E and H in only the top row of the newly sorted range to the original formula:
  Range("D" & rw + 1).FormulaR1C1 = "=RC[-1]+R[-1]C[4]" 'ORIGINAL
  Range("E" & rw + 1).FormulaR1C1 = "=IF(R[-1]C[5]<1,R[-1]C[1],IF(AND(R[-1]C[5]>1,R[-1]C[5]=INT(R[-1]C[5])),R[-1]C[1]+1,R[-1]C[1]))" 'original
  Range("H" & rw + 1).FormulaR1C1 = "=IF(RC[-4]>=INDEX('Daily Quota'!R2C2:'Daily Quota'!R201C2,MATCH(RC[-3],'Daily Quota'!R2C1:'Daily Quota'!R201C1,0)),(RC[-4]-SUM(INDEX('Daily Quota'!R2C2:'Daily Quota'!R201C2,MATCH(RC[-3],'Daily Quota'!R2C1:'Daily Quota'!R201C1,0)):INDEX('Daily Quota'!R2C2:'Daily Quota'!R201C2,MATCH(RC[-2]-1,'Daily Quota'!R2C1:'Daily Quota'!R201C1,0)))),R[-1]C+RC[-5])" 'original

Next rw
Application.ScreenUpdating = True
MsgBox "Done"
End Sub
 
Last edited:

HansrajR

Member
Just started learning VBA today

My apologies for syntax errors and any other errors.

A coding similar to below might work.

>>> use code - tags <<<
Code:
.Cells(rw, Start Date) is must be written as .Cells(rw, 5)

.Cells(rw, Not Before) is must be written as .Cells(rw, 11)

.Cells(rw, Not After) is must be written as .Cells(rw, 12)

.Cells(rw, Age) is must be written as .Cells(rw, 13)

Sub Main()

Dim rw As Long

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

         If .Cells(rw, Start Date) <= .Cells(rw, Not Before) Then
ActiveCell.EntireRow.Offset(1, 0).

Else

         If .Cells(rw, Start Date) >= .Cells(rw, Not Before) And .Cells(rw, Age) < 11 Then
ActiveCell.EntireRow.Offset(1, 0).

Else

         If .Cells(rw, Start Date) >= .Cells(rw, Not Before) And <= .Cells(rw, Not After) And .Cells(rw, Age) < 11 Then
ActiveCell.EntireRow.Offset(1, 0).

End If .Cells(rw, Start Date) > .Cells(rw, Not After)

    Next Row

End Sub
 
Last edited by a moderator:

HansrajR

Member
Sorry posted at the same time as you I believe. I will try the proposed solution and revert back. Could you kindly have a look at my previous reply.
 

HansrajR

Member
Tried the following code

Code:
Sub sequenceOptimizer()

'Processing Start Date: Column E - Column Number: 5
'Processing Not Before: Column K - Column Number: 11
'Processing Not After: Column L - Column Number: 12
'Processing Age at Processing Start Date: Column M - Coulumn Number: 13

Dim rw As Long

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To LastRow
        
        If .Cells(rw, 5) < .Cells(rw, 11) Then ActiveCell.EntireRow.Offset(1, 0).Activate
        If .Cells(rw, 5) >= .Cells(rw, 11) And .Cells(rw, 13) < 11 Then ActiveCell.EntireRow.Offset(1, 0).Activate
        If .Cells(rw, 5) >= .Cells(rw, 11) And .Cells(rw, 5) <= .Cells(rw, 12) And .Cells(rw, 13) < 11 Then ActiveCell.EntireRow.Offset(1, 0).Activate
        If .Cells(rw, 5) > .Cells(rw, 12) Then ActiveCell.EntireRow.Offset(-1, 0).Activate
        End If
    Next Row
End With

End Sub
 

HansrajR

Member
Still trying to get it right

Code:
Sub sequenceOptimizer()  

'Processing Start Date: Column E - Column Number: 5
'Processing Not Before: Column K - Column Number: 11
'Processing Not After: Column L - Column Number: 12
'Processing Age at Processing Start Date: Column M - Column Number: 13  

Dim rw As Long

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

    Do Until IsEmpty(ActiveCell)
        If .Cells(rw, 5) < .Cells(rw, 11) Then
            ActiveCell.EntireRow.Offset(1, 0).Select
        If .Cells(rw, 5) >= .Cells(rw, 11) And .Cells(rw, 13) < 11 Then
            ActiveCell.EntireRow.Offset(1, 0).Select
        If .Cells(rw, 5) >= .Cells(rw, 11) And .Cells(rw, 5) <= .Cells(rw, 12) And .Cells(rw, 13) <
            11 Then
            ActiveCell.EntireRow.Offset(1, 0).Select
        If .Cells(rw, 5) >= .Cells(rw, 11) And .Cells(rw, 5) > .Cells(rw, 12) Then
            ActiveCell.EntireRow.Offset(-1, 0).Select

        End If
    Next Row  
    Loop

End With

End Sub
 

p45cal

Well-Known Member
Gosh.
Two points to make, one about Activecell, the other about .UsedRange.
You can step through code using the F8 key while observing the sheet it's acting on. You click somewhere in the code and you press F8 on the keyboard.
Arrange the windows on your screen so that one half shows the sheet, and the other half the code (the VBE window).
Open the attached with sheet UsedRange1 showing in one half and the macro sequenceOptimizer1 in the other.
Don't make any changes to the sheet, but select cell E1
Click in the macro code somewhere and press F8 on the keyboard until the line:
LastRow = .UsedRange.Rows.Count
is highlighted in yellow. This is the line that is next going to be executed.
Before you press F8 again, use your mouse to hover over the text LastRow in the code. You should see a small pop-up with the text:
LastRow = Empty
This shows you the value in LastRow.
Now press F8 again, and once again hover over LastRow, you should see:
LastRow = 14
When we glance at the sheet we indeed note that the last row is row 14. This is what we expect.
Now press F8 again until the start of the first If statement is highlighted.
(You should still only have cell E1 selected at the moment.)
While we're here we'll also hover over rw in the code and see that its value is 4 (as expected).
Pressing F8 once more brings us to the second part of that same If statement (because with the values in the sheet the first part of that statement is TRUE) and pressing F8 again now activates the 2nd row on the sheet. Perfect.
We don't click on anything on the sheet, we leave things exactly as the code left them.
We're now going to run through the rest of the code in one fell swoop by pressing on F5 on the keyboard and we'll make a note of what's selected when it's finished.
Do it now.
We end up with row 9 being selected.

Now we'll run the whole macro again, but before doing so, we'll select cell E20 on the sheet.
Now click somewhere in the code and press F5 on the keyboard to run the whole macro at once.
What row is selected now? (It should be row 28.) Not useful.

Hopefully, this shows the unreliablity of depending on the ActiveCell.
You very, very rarely need to select/activate anything in code. I use it temporarily, during development, to give me a good visual clue/confirmation that my code is acting on the correct cells. You can see an example of this in my code in msg#6:
Code:
'Range("A" & rw + 1 & ":M190").Select ' just shows you which rows will be sorted (no headers)
Currently it is commented-out (inactivated) by the leading apostrophe.

To remove our dependence on what happens to be selected/activated on the sheet, instead of the likes of:
ActiveCell.EntireRow.Offset(1, 0)
we can use something along the lines of:
.cells(rw,5).EntireRow.Offset(1, 0)
Note that I haven't put anything after these code lines because we haven't decided what you're going to do with them, so for the moment we'll just select them so that we can see what the code is looking at:
.Cells(rw, 5).EntireRow.Offset(1, 0).Select
which can be shortened if you want to:
.Cells(rw+1, 5).EntireRow.Select
I've adjusted your code in the macro sequenceOptimizer2. Step through it with F8 while observing what changes on the sheet. Note that it no longer matters what is selected before the macro is run, it behaves in exactly the same way. We've lost that dependence on the active cell.

Moving onto .UsedRange.
It's 9 out of 10 times reliable because very commonly the used range on a sheet includes row 1, the very top row of the sheet, but it ain't always so, so let's look at the sheet UsedRange2 in the attached.
With that sheet active, we'll step through the macro sequenceOptimizer2 with F8:
Just after executing the line:
LastRow = .UsedRange.Rows.Count
hovering over LastRow shows it has a value of 6. Yes, because the usedrange on this sheet (as long as you haven't added anything to it) is E9:L14 which only has 6 rows.
So when we come to the next line:
For rw = 4 To LastRow
that translates to:
For rw = 4 To 6
and when you step through the rest of the code, the code is looking at cells which don't even have any values in.

There are various ways to determine what cells/rows code should operate on, but which one you choose depends a lot on what's likely to be on the sheet.
On the current sheet, using the keyboard, you could do the following:
Select cell E9 (top left cell of the range), then press End, followed by the down arrow on the keyboard. We end up on cell E14. We can do that in code too. But wait, what if, say, cell E11 is blank? We only end up on cell E10. Not good.
Instead, let's try selecting the cell in the very bottom of the sheet in column E (cell E1048576), pressing End, then the up arrrow. We end up on cell E14. Great. Except if there's something below the bottom of the table we want to work on.
OK, let's try selecting a cell within the table, pressing F5 on the keyboard (or Ctrl+G) to bring up the Go To dialogue box, click on Special… then double-click on the Current region option. That gives us a range we can work on - as long as we can guarantee that there's a completely blank row, and a completely column, around the range (or the edge of the worksheet) you want to work on.
Those three methods coded for:
Code:
With ActiveSheet
  'method 1
  LastRow = .Range("E9").End(xlDown).Row

  'method 2
  LastRow = .Range("E1048576").End(xlUp).Row
  'or:
  LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

  'method 3
  Set myRange = .Range("F10").CurrentRegion
  LastRow = myRange.Cells(myRange.Cells.Count).Row
End With
There's loads on the internet; do a search for Excel vba finding the last row
Some it came up with:
VBA Tutorial: Find the Last Row, Column, or Cell in Excel
VBA Last Row | Top 3 Methods to Find the Last Used Row?
How to Find Last Row, Column, and Cell using VBA in Excel

Now onto the 3rd of my 2 points.
I noticed you have some comments:
'Processing Start Date: Column E - Column Number: 5
'Processing Not Before: Column K - Column Number: 11


Clearly a reminder to you of what's where on the sheet.
There's another notation for the likes of:
.Cells(rw, 5)
and it's:
.Cells(rw, "E")

but also useful to the coder is the likes of:
StartDate = "E"
NotBefore = "K"
NotAfter = "L"


'then later refer to cells like:
.cells(rw,StartDate)…
.cells(rw,NotBefore)…


which makes for more readable code.

Now you can probably tell it's taken me some time to produce this guff and it's barely addressed the real raison d'etre of this thread. I can't continue to act as tutor in this way.
You/We/I need to get back to dealing with your problem. So could you answer my chuffing questions?
 

Attachments

Last edited:

HansrajR

Member
p45cal thanks for devoting so much time to my problem.

The macro sequenceOptimizer2 is the best code to begin with.

To improve the code each IF Statement must be satisfied (looping) before moving to the next IF Statement in order of hierarchy.

The worksheet must be refreshed to allow re-calculation of cells before moving to the next IF Statement.

Refreshing active worksheet using

ActiveWorkbook.RefreshAll

or

Workbooks("Sample.xls").RefreshAll as recommended in your previous reply.

The last IF Statement offsets the row up unlike proceeding IF Statements, returning the row to the position where the Start Date is between Not Before and Not After despite Age <11.

This allows the code to provide the best possible sequence.

Code:
Sub sequenceOptimizer2()

'Processing Start Date: Column E - Column Number: 5
'Processing Not Before: Column K - Column Number: 11
'Processing Not After: Column L - Column Number: 12
'Processing Age at Processing Start Date: Column M - Coulumn Number: 13

Dim rw As Long

With ActiveWorkbook.ActiveSheet
  LastRow = .UsedRange.Rows.Count
  For rw = 4 To LastRow
    .Cells(rw, 5).Select 'temporary line to show the coder which cell is being looked at by the code
    If .Cells(rw, 5) < .Cells(rw, 11) Then .Cells(rw, 5).EntireRow.Offset(1, 0).Select
    If .Cells(rw, 5) >= .Cells(rw, 11) And .Cells(rw, 13) < 11 Then .Cells(rw, 5).EntireRow.Offset(1, 0).Select
    If .Cells(rw, 5) >= .Cells(rw, 11) And .Cells(rw, 5) <= .Cells(rw, 12) And .Cells(rw, 13) < 11 Then .Cells(rw, 5).EntireRow.Offset(1, 0).Select
    If .Cells(rw, 5) > .Cells(rw, 12) Then .Cells(rw, 5).EntireRow.Offset(-1, 0).Select
' End If
Next rw ' Row
End With

End Sub
I thought to have replied to your questions in your previous reply. Could you kindly point to answers I did not provide sufficient details.
 

p45cal

Well-Known Member
.Select does nothing more than select cells on a sheet. Nothing else. You will have to do something more.
The worksheet must be refreshed to allow re-calculation of cells before moving to the next IF Statement.
Where does this come from?
and:
Workbooks("Sample.xls").RefreshAll as recommended in your previous reply.
Where was that? I don't remember saying that. From Excel's help:"Refreshes all external data ranges and PivotTable reports in the specified workbook." Do you have any of those in the workbook?
Unless you have set Calculation to Manual in the workbook, re-calculations take place automatically, with code usually waiting for it finish before moving on.
The last IF Statement offsets the row up unlike proceeding IF Statements, returning the row to the position where the Start Date is between Not Before and Not After despite Age <11.
It does nothing of the sort, it just selects it on the sheet. Just as if you selected that range of cells with a mouse. No moving of rows takes place.

I learnt my VBA with the help of a book by John Walkenbach called Excel 2003 Power Programming with VBA. He's done several versions over the years, up to Excel 2010, since then the books seem to have been co-written by others, namely Michal Alexander and Dick Kusleika. I found the writing style quite readable and it quite quickly involves you doing hands-on exercises. Doing an internet search for these books reveals a few links:
Excel 2019 Power Programming with VBA: Alexander, Michael, Kusleika, Dick: 9781119514923: Amazon.com: Books
Excel 2019 Power Programming with VBA | Wiley
I also found this link:
(PDF) Excel 2016 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) - Michael Alexander.pdf | Minh nd - Academia.edu
which (I hope it's not pirated) allows you to look at whole chunks of the book on-screen (I found working with a book much better than working with an on-screen version - I was forever juggling for screen space with the electronic version - but if there's a Kindle version that might work). If you scroll down that page (it's a long one) you will get to a section called Working with Ranges which might be relevant to what you're doing - but I'd recommend reading a fair bit before that section too. The differences between 2016 and 2019 are minimal as far as VBA is concerned.
Look really hard and you may find a download somewhere but do make sure it's not got malware associated with it.
Could you kindly point to answers I did not provide sufficient details.
Sure,
from msg#3:
1. which one of the rows you'd choose to process the very first and why.
same from msg#6:
I can work out some of the answers to my questions from your reply, but not all; how for instance, is the first line chosen?
Question 2 in msg#3 I can mostly work out from your answers, but not, for example, where you have several rows which might all be suitable to be taken as the next batch to process, which one of those rows you'd finally choose, and most importantly why?
You answered question 2 largely by referring to your table, with its calculations; I was looking for you to explain how you'd select which batch to process next, as if you didn't have a computer, never mind Excel.

In msg#6 I provided some code for you to try, so far you have not commented.
 

HansrajR

Member
I created a flow diagram to illustrate the process.

Instead of using LastRow, the last row can be set to Row Number 3001 since maximum number of batches is 3000.

To reply to your questions:

Firstly, the spreadsheet was sorted using simple Excel Multiple Levels Sorting in the beginning by Not Before date, then by Not After date and lastly by Grow start date and then formula was inserted for calculated columns.

In ideal conditions, this simple sorting without using VBA macro should have given the ideal sequence.

However, in the previous year the Not Before and Not After dates criteria was not respected to process the Batches.

Therefore, when same Not Before and Not After time periods are applied in current year, Age in the following year (which is difference between Processing Start Date in current year and Grow Start date (equal to Processing Start date in previous year) are less than 11 months for some batches.

Then comes in the VBA macro to find the ideal sequence by starting processing with Row Number 4 and moving to the next row simply in ascending Row Number up to the last non blank row.

In the end, the macro ensures the Processing start date is always between Not Before and Not After dates with Age greater or equal to 11.

On event, the criteria Age >= 11 cannot be satisfied by keeping Processing start date between Not Before and Not After dates, the row is moved upwards until Processing start date is between Not Before and Not After dates. In other words, Best before date.

I tried the code you provided, it has given me far better insight on how to define the process given my limited experience in VBA Macro for which I am very grateful to the forum and your devotion.


74724
 

Attachments

HansrajR

Member
I was able to come up with the following code, but has errors

Code:
Sub sequenceOptimizerHansrajR()

'Processing Start Date: Column E - Column Number: 5
'Processing Not Before: Column K - Column Number: 11
'Processing Not After: Column L - Column Number: 12
'Processing Age at Processing Start Date: Column M - Coulumn Number: 13

Dim rw As Long

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To LastRow
    
    .Cells(rw, 5).Select
        
        If .Cells(rw, 5) < .Cells(rw, 11) Then .Cells(rw, 5).EntireRow.Select 'Start date less than Not Before date
            Selection.EntireRow.Cut 'Cut Entire row
            Selection.Insert Shift:=xlDown
        Do Until .Cells(rw, 5) >= .Cells(rw, 11)
    Loop
        
        If .Cells(rw, 13) < 11 Then .Cells(rw, 5).EntireRow.Select
            Selection.EntireRow.Cut 'Cut Entire row
            Selection.Insert Shift:=xlDown
            Do Until .Cells(rw, 13) >= 11
    Loop
    
        If .Cells(rw, 5) > .Cells(rw, 12) Then .Cells(rw, 5).EntireRow.Select
            Selection.EntireRow.Cut 'Cut Entire row
            Selection.Insert Shift:=xlUp
            Do Until .Cells(rw, 5) <= .Cells(rw, 12)
    Loop
        
Next rw
End With

End Sub
 
Last edited:

HansrajR

Member
Improved Flow diagram and code (but errors in code)

Sample file in attachment

Code:
Sub sequenceOptimizer()

'Start Date date: Column E - Column Number: 5
'Not Before date: Column K - Column Number: 11
'Not After date: Column L - Column Number: 12
'Age at Start Date: Column M - Coulumn Number: 13

Dim rw As Long

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To LastRow
 
    .Cells(rw, 5).Select
      
        If .Cells(rw, 5) < .Cells(rw, 11) Then 'If Start date less than Not Before date
            Do Until .Cells(rw, 5) >= .Cells(rw, 11) 'Continue loop until Start date is greater or equal to Not Before date
            .Cells(rw, 5).EntireRow.Select 'Select entire row
            Selection.EntireRow.Cut 'Cut Entire row
            Selection.Insert Shift:=xlDown 'Insert one row down
            
            Call Copy_formula_from_rowNumber3_paste_up_to_last_row
            
            Loop
            
      
        If .Cells(rw, 13) < 11 Then 'If Age at Start date is smaller than 11
            Do Until .Cells(rw, 13) >= 11 'Continue loop until Age at Start date is greater or equal to 11
            .Cells(rw, 5).EntireRow.Select 'Select entire row
            Selection.EntireRow.Cut 'Cut Entire row
            Selection.Insert Shift:=xlDown 'Insert one row down
            
            Call Copy_formula_from_rowNumber3_paste_up_to_last_row
            
            Loop
            
            
        If .Cells(rw, 5) > .Cells(rw, 12) Then 'If Start date is greater than Not After date
            Do Until .Cells(rw, 5) <= .Cells(rw, 12) 'Continue loop until Start date is less than or equal to Not After date
            .Cells(rw, 5).EntireRow.Select 'Select entire row
            Selection.EntireRow.Cut 'Cut Entire row
            Selection.Insert Shift:=xlUp 'Insert one row down
            
            Call Copy_formula_from_rowNumber3_paste_up_to_last_row
            
            Loop
            
        End If
      
Next rw
End With

End Sub
Sub Copy_formula_from_rowNumber3_paste_up_to_last_row()
'
' Copy_formula_from_rowNumber3_paste_up_to_last_row Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Range("D3:J3").Select
    Selection.Copy
    Range("D4:J190").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-189
    Range("M3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("M3:M190").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    Range("A1").Select
    Application.CutCopyMode = False
End Sub


74733
 

Attachments

HansrajR

Member
Got it working, but looping endlessly.

Code:
Sub sequenceOptimizer()

'Start Date date: Column E - Column Number: 5
'Not Before date: Column K - Column Number: 11
'Not After date: Column L - Column Number: 12
'Age at Start Date: Column M - Coulumn Number: 13

Dim rw As Long

With ActiveWorkbook.ActiveSheet
    LastRow = .UsedRange.Rows.Count
    For rw = 4 To 500
 
    .Cells(rw, 5).Select
      
        If .Cells(rw, 5) < .Cells(rw, 11) Then 'If Start date less than Not Before date
            Do Until .Cells(rw, 5) >= .Cells(rw, 11) 'Continue loop until Start date is greater or equal to Not Before date
            .Cells(rw, 5).EntireRow.Select 'Select entire row
            Selection.Cut 'Cut Entire row
            Selection.Offset(1, 0).Select
            Selection.Insert Shift:=xlDown 'Insert one row down
            
            Call Copy_formula_from_rowNumber3_paste_up_to_last_row
            
            Loop
            
        End If
            
      
        If .Cells(rw, 13) < 11 Then 'If Age at Start date is smaller than 11
            Do Until .Cells(rw, 13) >= 11 'Continue loop until Age at Start date is greater or equal to 11
            .Cells(rw, 5).EntireRow.Select 'Select entire row
            Selection.Cut 'Cut Entire row
            Selection.Offset(1, 0).Select
            Selection.Insert Shift:=xlDown 'Insert one row down
            
            Call Copy_formula_from_rowNumber3_paste_up_to_last_row
            
            Loop
        
        End If
            
            
        If .Cells(rw, 5) > .Cells(rw, 12) Then 'If Start date is greater than Not After date
            Do Until .Cells(rw, 5) <= .Cells(rw, 12) 'Continue loop until Start date is less than or equal to Not After date
            .Cells(rw, 5).EntireRow.Select 'Select entire row
            Selection.Cut 'Cut Entire row
            Selection.Offset(-1, 0).Select
            Selection.Insert Shift:=xlUp 'Insert one row down
            
            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")

End Sub
Sub Copy_formula_from_rowNumber3_paste_up_to_last_row()
'
' Copy_formula_from_rowNumber3_paste_up_to_last_row Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Range("D3:J3").Select
    Selection.Copy
    Range("D4:J190").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-189
    Range("M3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("M3:M190").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    Range("A1").Select
    Application.CutCopyMode = False
End Sub
 

p45cal

Well-Known Member
In a given row, is it required that
  • only the Processing Start Date be within the Processing Not Before date and the Processing Not After date
  • or both the Processing Start Date and the Computed End Cut Date should be within the Not Before/Not After dates?
 

p45cal

Well-Known Member
That helps.
I've been trying a few things but so far struggling to get all rows to conform.
For example, I get to the stage that I can't find a row >11 months and within the processing dates, but, if we were to wait a bit (instead of processing the next batch straight after Computed End Cut Date) the next row could fulfil all conditions. Is this a possiblity.?

I'm curious (although it could help me make sensible decisions in the code), what industry are we in here? With phrases like Grow Start, cutting, it feels as though it could be plant based - wheat, rice, mushrooms(?!), hay?, some commodity which need to be harvested. So what is this Tonnage that is being processed?
 

HansrajR

Member
It's in the sugarcane industry. Tonnage is the the number of tons of sugarcane for each field.

W.r.t. to your question:

I've been trying a few things but so far struggling to get all rows to conform.
For example, I get to the stage that I can't find a row >11 months and within the processing dates.

That's why I included the 3rd loop.

1st loop, makes sure the Start Date is greater or equal to Not Before date. When this condition is satisfied, the 2nd loop is initiated.

2nd loop makes sure the age is greater is equal to 11. Once this condition is satisfied, the 3rd loop is initiated.

In the 3rd loop, on event, Not After date was exceeded by the 2nd loop, the 3rd loop returns the row back up (instead of down as in the previous loops - one row at a time) until the Start date is between Not Before and Not After date irrespective of the condition of age greater or equal to 11.
 

p45cal

Well-Known Member
First regarding your code:
It's not doing what you think it's doing:
The first and second loops, if they're not satisfied, don't actually move any rows at all; they're in the same position after:
Code:
Selection.Cut    'Cut Entire row
Selection.Offset(1, 0).Select
Selection.Insert Shift:=xlDown    'Insert one row down
as they were in before.

The third loop does swap positions of two rows, but if after one swap it's still not satisfied, it loops and swaps the same two rows back again!

So it's no surprise that you'll get in to an endless loop.

What you're trying to do is, of course, to sort the data, but Excel can do that, and can sort the entire table in less time than it takes your code to swap three or four rows around - the built-in sort engine is always going to be enormously faster than any code you or I could write.

Your code, or something like it, can be more specific and cater for a wide range of circumstances, and if the data is brought into memory rather than manipulated multiple times by writing to and reading from the sheet, it can be very fast. But as you develop code you need to know exactly what all factors are that go to decide the best order - that's what @vletm was asking about in msg#2, and I still don't know it, what's more I don't think you appreciate the finer aspects either!
So what I'm trying to do, is find a solution that, if not perfect, is pretty good, even though it may take some time to run, by coding in a way which can easily be adjusted (the sort order and what's sorted; you can see that in this snippet:
Code:
.SortFields.Add2 Key:=Range("K" & rw + 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add2 Key:=Range("L" & rw + 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add2 Key:=Range("B" & rw + 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add2 Key:=Range("M" & rw + 1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
where you can add new lines, delete old ones, change the order they're executed in etc.). The aim being that the top row, after sorting, has to be the best row.
What's more, you can get inventive with other calculated columns, for example, if there isn't something >11 months, we can try to ensure that we pick a row nearest to 11 months, or we can observe a small Not Before/Not After window and choose to prioritise those rows so that later we're not left with them when they're impossible to place. This sort of stuff is easy to implement by small changes in code and tweaks to calculated columns. I wouldn't like to try coding for those changes/trials purely in VBA.
But, having said all that, we'll probably find we arrive at a 'best' sequence fairly quickly, and if you still want to write alternative code to speed up the process or to add some special tweaks to 'best' sequence, you've got 2 things going for you:
1. You got good results to compare you new code's results with.
2. You'll have a very clear idea of all the intricacies/algorithms needed in the code.

(Just as an aside, is the time taken for this sequencing very important? It seeems to me that this is a process you don't have to do more than once a year, so it doesn't really matter if it takes 10 minutes or if it has finished before your finger has lifted off the mouse button.)

Now with regard to my question, yep, you've done it again; While your answer let's me glean more information about your best sequence, it hasn't answered:
but, if we were to wait a bit (instead of processing the next batch straight after Computed End Cut Date) the next row could fulfil all conditions. Is this a possiblity.?
If processing can be delayed it might allow more (or all?) rows to fulfil the requirements. Perhaps the industry doesn't work like that and everyone has to be hands on in one mad session/season. Realise that I don't automatically know the answer to these because I'd need to know stuff like: can the tonnage production (cutting?) on the Daily Quota sheet be delayed, and can production from the Daily Quota sheet be stored while it's waiting to be processed (maybe it HAS to be processed asap), maybe there's minimal storage available - I just don't know.

I don't have to know all this stuff, and I can keep on working to get a good-ish solution (I'm not full time on this) and will do (I like trying to be ingenious), but it helps.

[These messages are getting ridiculously long]
 

p45cal

Well-Known Member
Re. waiting a bit.
In tests I got to the stage where by the 4th Sept, 80+ rows had been processed.
All of the remaining rows had Not Before/Not After windows starting after the 15th Sept.
What other solution is there but to do nothing until 15th Sept which not only keeps batches within dates but also helps to ensure that the >11 months is satisfied too?
 

HansrajR

Member
If all remaining rows have Not Before/No After window starting after 15 Sept, they can be sorted by Grow Start date oldest to newest, so doing the age will be in descending order.

(Sugarcane less than 11 months are immature and smaller sucrose content).

Thanks to pointing out issues with the code. I am working on it.

This exercise is done only once in a year so sequencing time is not a problem.

To reply to your question if processing time can be delayed to our more rows to fullfil requirements - No the processing has to continue without breaks.

Working on replying to the rest of your question.
 
Top