Fermentators used date: find next available based on a date

[BertinL]

Hi, sorry for the title but i couldn t find a way to explain it...

Basically i am making an excel to be able to make the most efective production calendar for my brewery...

I have entries like the ones here under:
[TABLE="width: 500"]
<tbody>[TR]
[TD]CookingDate[/TD]
[TD]Fermentator Name[/TD]
[TD]IsAvailableOn[/TD]
[/TR]
[TR]
[TD]03/08/2015[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="width: 91"]Fermentator 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11/08/2015[/TD]
[/TR]
[TR]
[TD]04/08/2015[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]Fermentator 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12/08/2015[/TD]
[/TR]
[TR]
[TD]05/08/2015[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]Fermentator 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13/08/2015[/TD]
[/TR]
[TR]
[TD]11/08/2015[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="width: 91"]Fermentator 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1/9/2015[/TD]
[/TR]
[TR]
[TD]12/08/2015[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]Fermentator 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20/8/2015[/TD]
[/TR]
[TR]
[TD]13/08/2015[/TD]
[TD]Fermentator 3[/TD]
[TD]3/9/2015[/TD]
[/TR]
[TR]
[TD]20/08/2015[/TD]
[TD] ?????[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
What i want to find is which fermentator will be available on the 20th of august (Fermentator 2).

I ve tried many ways but when i thought i found it... i got errors in the next rows when for example 2 fermentation tanks ends on the same day...

I made a VBA Code that find the best value (recursive stuff) but wel... after a 100 lines it takes 10 to 15 minutes...

If someone as an idea... I am open

 

[mole999]

post your code, it sounds as if it could be made more efficient

 

[BertinL]

As i said it is far from being good... one of my first vba code.

So it is pretty uggly not optimized at all, just wanted to have it giving me results first...

data1 is the range with fermentator names
data2 is the range with availableDates
mydate is the date for which i have to find the closest Fermentation Tank.

Function Get_NextFreeTank(Data1 As Range, Data2 As Range, myDate As Date) As String

    If Data1.Rows.Count <> Data2.Rows.Count Then
       Get_NextFreeTank = "Different range sizes"
       Exit Function
    ElseIf Data1.Rows.Count = 1 Then
       Get_NextFreeTank = "Single cell range"
       Exit Function
    End If


    Dim MyDictionary As Scripting.Dictionary
    Dim dDates As New Scripting.Dictionary
    Debug.Print "---------- Get_NextFreeTank ---------------"
    Dim stempVal As Date
    Dim svalue As Variant, sTank As String
    
    Dim lowestDate As Date, tmpDate As Date
    
    Dim intDayRange As Integer
    
    intDayRange = 31
    
    
    i = 0
    For Each cell In Data1
        i = i + 1
        Set datecell = Data2.Cells(i, 1)
        
        Debug.Print "Values to check if empty are // " & cell.value & " // " & datecell.value & "//"
        
        If Not (IsEmpty(cell.value) Or IsEmpty(datecell.value)) Then
        
        '   Check only for dates in range of one month
            tmpDate = DateValue(Format(datecell.value, "d/m/yyyy"))
            If Abs(DateDiff("d", tmpDate, myDate)) < intDayRange Then
                    If dDates.Exists(cell.value) Then
                       
                        Debug.Print "---------- Comparing Values " & cell.value & " with value " & dDates(cell.value) & " to " & datecell.value
                        If dDates(cell.value) < DateValue(datecell.value) Then
                            dDates(cell.value) = datecell.value
                            Debug.Print "---------- Changing value to  " & datecell.value
                        End If
                    Else
                        Debug.Print "---------- ADD " & cell.value & " with value " & datecell.value
    
                        dDates.Add cell.value, DateValue(datecell.value)
    
                    End If
            End If
        End If
    Next


    
'
    lowestDate = DateValue(Format("31/12/2039", "d/m/yyyy"))
    sTank = "unknown"
    For Each svalue In dDates
            Debug.Print "DICT ID are " & svalue & " with value " & dDates(svalue)
            If DateValue(Format(dDates(svalue), "d/m/yyyy")) < lowestDate Then
                lowestDate = DateValue(Format(dDates(svalue), "d/m/yyyy"))
                sTank = svalue
                Debug.Print "Lowest Date Changed"
            End If
            
    Next
    
    Get_NextFreeTank = sTank
    Debug.Print "---------- END function ---------------"
End Function

Basically i could have a simple way of optimizing it by searching max values for each fermentation tank i got before the line where he call to the vba code is entered.


Now i am trying to see if i could adapt something using that trick: https://www.reddit.com/r/excel/comments/34riqf/vlookup_from_the_bottom_of_the_column/


In database query i would have done something like

select top 1 fermentation from 
(Select fermentation, 
         max(availabledate)
)
order by datediff("d",mydate,availabledate)