populate values in a cell based on date criteria

[mogss_04]

Hi All,

I required assistance on the below. Would be great if someone could help me out with it.

I have the below file.

https://www.dropbox.com/s/5wzide7bso...%203.xlsx?dl=0

I have to look at the below columns
I - Usage , column L - Batch creation Date , M - Batch Expiry Date and N - Inventory Flag

There is a coulmn name "Usage - I" which has the below 7 categories data . So 1st case we need to filter first by "Unrestriced use" and "Unrestricted-Use Mat" and then need to filter column "M - Batch Expiry Date". So if the expiry date falls 12 months after the the current month i.e 2020 June onwards then column N should be polpulated as"Usable (>12)", if expiry date falls between 7 - 12 months after expiry date
which is from December 2019 - May 2020 then column N should be polpulated as"Usable (7-12)" . Under "Unrestriced use" and "Unrestricted-Use Mat" anything before May 2019 that is current month should be populated as
"Expired" and if expiry date is from " May 2019 - November 2019" then column N should be poulated as "Near expiry".
Next we need to filter by "Blocked Stock" under column I and if it is Blocked stock then column N should be populated as Blocked irrespective of the expiry date column - M . Similary if column I is "Transit and Intransit" then column N should be populated as "Transit".Similary if column I is "Transit and Intransit" then column N should be populated as "Transit".Quality inspection - to be populated as "Quality inspection" under column N irrespective of expiry date
Valuated Goods Receipt Blocked Stock- to be populated as "Blocked" under column N irrespective of expiry date




1 Unrestricted Use
2. Transit
3.Blocked Stock
4.Unrestricted-Use Mat
5. Intransit
6.Quality inspection
7.Valuated Goods Receipt Blocked Stock


When " Batch Expiry Date" is blank or # we need to look at the "Batch Creation Date" column which is column "L" and the same rules apply. Exactly the same as expiry date rules except this goes 1 year back.


which is June 2018 onwards - Usable (>12)
Dec 2017 - May 2018 - Usable (7-12)
May 2017- Nov 2017 - Near Expiry
Before May 2017 -




If Manufacturing date and expiry date both are blanks or # present in both then N column should be poulated as Usable > 12.


Let me know how can we code the above and automate this procedure.

 

[DanteAmor]

Hi Dante,

I tried all possible tests on sign changes which you highlighted above on below code

" sh.Range("B1").AutoFilter Field:=12, Criteria1:=">" & wDate, Criteria2:="<" & wDat2 If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
sh.Range("N2:N" & lr).Value = "Usable (7-12)" "

However the 6/1/2020 is still being populated as "Usable (7-12)" instead of "Usable (> 12)".

The next period where it starts and where it ends.

if expiry date falls between 7 - 12 months after expiry date which is from December 2019 - May 2020 then column N should be polpulated as"Usable (7-12)"

Today is June 4.
Then the period goes from December 4, 2019 to June 4, 2020.
OR
The period runs from December 1, 2019 to May 31, 2020.
In your rules you only put month-year, but to make the filter that must have day-month-year.


I need for each rule put start: day-month-year, end: day-month-year.
Maybe it's clear to you, but I do not know your process, so I had to put the day, in this case adding 6 months to the current day.


I await your comments.

 

[mogss_04]

Hi Dante,

I was performing this for May month. Like wise I would be performing every month.

1.So if the expiry date falls 12 months after the the current month i.e 1st June 2020 on wards any date then column N should be populated as"Usable (>12)",

2.If expiry date falls between 7 - 12 months after expiry date which is from 1st December 2019 -31st May 2020 then column N should be populated as"Usable (7-12)" .
3. Under "Unrestricted use" and "Unrestricted-Use Mat" anything before 1st May 2019 that is current month should
4. "Expired" and if expiry date is from " 1st May 2019 - 30th November 2019" then column N should be populated as "Near expiry".

I hope this makes a little bit clear. Let me know you require any more clarifications from my side.

 

[DanteAmor]

Hi Dante,

I was performing this for May month. Like wise I would be performing every month.

1.So if the expiry date falls 12 months after the the current month i.e 1st June 2020 on wards any date then column N should be populated as"Usable (>12)",

2.If expiry date falls between 7 - 12 months after expiry date which is from 1st December 2019 -31st May 2020 then column N should be populated as"Usable (7-12)" .
3. Under "Unrestricted use" and "Unrestricted-Use Mat" anything before 1st May 2019 that is current month should
4. "Expired" and if expiry date is from " 1st May 2019 - 30th November 2019" then column N should be populated as "Near expiry".

I hope this makes a little bit clear. Let me know you require any more clarifications from my side.

The tests were successful in May, but now we are June 4, and June 1 is between 7-12 months.

 

[mogss_04]

Hi Dante,

Thanks for making it clear.I guess then same applies to then the dates which are appearing as "Expired" instead of "Near Expiry "

I would really be thankful if you could help solve the below as well since its on the same line but for other region.


This is for West file.West file link is below.


https://www.dropbox.com/s/437b14jhqd...West.xlsm?dl=0


My next region file involves the below. I will send you the file across for refrence.




path : "C:\Users\Priyanka Singh\Desktop\VBA code1"


I.)First we need to look at Column H - Expired Qty column. Unfilter "0" qty. Then look at "Batch Expiry Date" .






1)December 2019 onwards- Coulmn W should be populated as "Expired" and Column X to be populated as "Restricted"




2)May 2019 - November 2019 - Coulmn W should be populated as "Expired" and Column X to be populated as "Near Expiry"




3)anything before May 2019 - Coulmn W should be populated as "Expired" and Column X to be populated as "Expired"




Next look at column J- Near Expiry Qty. Unfilter "0" qty. For all these quantities Coulmn W should be populated as "Near Expiry" and Column X to be populated as "Near Expiry"




Next look at column N - NET Qty. Unfilter "0" qty.




1. June 2020 onwards - Usable (>12)




2. Dec 2019 - May 2020 - Usable (7-12)




And then same "When "expiry date" is blank or # we need to look at the MFG date and the same rules apply. Exactly the same as expiry date rules except this goes 1 year back.




which is June 2018 onwards - Usable (>12)
Dec 2017 - May 2018 - Usable (7-12)
May 2017- Nov 2017 - Near Expiry
Before May 2017 -




If Manufacturing date and expiry date both are blanks or # present in both then W - Usable and Column X column should be poulated as Usable > 12

Thank you so much for your assistance once again.

 

[DanteAmor]

Hi Dante,

Thanks for making it clear.I guess then same applies to then the dates which are appearing as "Expired" instead of "Near Expiry "

.

I am happy to help you with your requirements, but before you can comment me if the original requirement was covered.
If so, you could create a new thread for your next requirement.

 

[mogss_04]

Hi Dante,

I understood the above and it works for me , but just wanted to know whether can it be considered as month start for the period starting from and month end for period ending for "Usable (7-12)" and "Near Expiry" in the month in which i run and not the date on which i run .

As in you said it is taking June 4 since we running on that date.Just wanted to know irrespective of the date we run in June it should be considered as June start.

Let me know if I am making sense and if that is possible.

 

[DanteAmor]

Hi Dante,

I understood the above and it works for me , but just wanted to know whether can it be considered as month start for the period starting from and month end for period ending for "Usable (7-12)" and "Near Expiry" in the month in which i run and not the date on which i run .

As in you said it is taking June 4 since we running on that date.Just wanted to know irrespective of the date we run in June it should be considered as June start.

Let me know if I am making sense and if that is possible.

Please, try this

Sub populate_values()
    Dim sh As Worksheet, wDate As String, wDat2 As String, lr As Long, lr2 As Long, fin As Variant
    Set sh = Sheets("base")
    
    Application.ScreenUpdating = False
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Unrestricted Use", Operator:=xlOr, Criteria2:="=Unrestricted-Use Mat"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 6, 1), "mm/dd/yyyy")
    fin = Day(DateSerial(Year(Date), Month(Date) + 12, 1) - 1)
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 12 - 1, fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date) + 12, 1), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), 1), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:="<" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
    
    wDate = Format(DateSerial(Year(Date), Month(Date), 1), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date), Month(Date) + 6, fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=12, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Blocked Stock"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Blocked"


    sh.Range("B1").AutoFilter Field:=8, _
        Criteria1:="=Transit", Operator:=xlOr, Criteria2:="=CC In-Transit"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Transit"


    sh.Range("B1").AutoFilter Field:=8, Criteria1:="=Quality Inspection"
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Quality Inspection"
    
    '****
    'When " Batch Expiry Date" is blank or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date), 1), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (>12)"
    
    
    wDate = Format(DateSerial(Year(Date) - 1, Month(Date) - 6, 1), "mm/dd/yyyy")
    wDat2 = Format(DateSerial(Year(Date) - 1, Month(Date) - 1, fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable (7-12)"
        
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date) - 1, 1), "mm/dd/yyyy")
    fin = Day(DateSerial(Year(Date) - 2, Month(Date) + 6, 1) - 1)
    wDat2 = Format(DateSerial(Year(Date) - 2, Month(Date) + 5, fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:=">=" & wDate, Operator:=xlAnd, _
                                         Criteria2:="<=" & wDat2
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Near expiry"
    
    
    fin = Day(DateSerial(Year(Date) - 2, Month(Date) - 1, 1) - 1)
    wDate = Format(DateSerial(Year(Date) - 2, Month(Date) - 2, fin), "mm/dd/yyyy")
    sh.Range("B1").AutoFilter Field:=11, Criteria1:="<=" & wDate
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Expired"
        
    
    '****
    'If Manufacturing date and expiry date both are blanks or #
    '****
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("B1").AutoFilter Field:=12, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    
    sh.Range("B1").AutoFilter Field:=11, _
        Criteria1:="=#", Operator:=xlOr, Criteria2:="="
    If sh.Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
        sh.Range("N2:N" & lr).Value = "Usable > 12"
    
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    MsgBox "Done"


End Sub

Try the first file that you sent me and tell me if you have problems with some date.

 

[mogss_04]

Hi Dante,

I ran the test on the previous file assuming June is the month we running. Below are the certain mismatches

Batch Expiry date

1.)The below expiry dates falling in June 2020 are being populated as "Usable (>12)" instead of "Usable (7-12)"
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]
01.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]30.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]15.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]19.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]21.06.2020[/TD]
[/TR]
</tbody>[/TABLE]



Batch Creation Date (When Expiry date is blank or #)

1.) 05.06.2018 is being populated as "Usable (>12)" instead of "Usable (7-12)"

2.) 12.12.2017 is being populated as "Usable (7-12)" instead of "Near Expiry"

3.) 05.05.2017 is populated as "Near Expiry" instead of "Expired"
Also as per post #3 the below problem still exists

"[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]When the usage is "Blocked Stock" Column N is being populated as "Expired" instead of "Blocked". These are for cases where Batch Expiry date is "#". I guess its getting populated on the basis of the code which is run later where Expiry date is "#". It should appear as Blocked irrespective of Expiry date when Usage is " Blocked Stock". "[/COLOR]

 

[DanteAmor]

Hi Dante,

I ran the test on the previous file assuming June is the month we running. Below are the certain mismatches

Batch Expiry date

1.)The below expiry dates falling in June 2020 are being populated as "Usable (>12)" instead of "Usable (7-12)"
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64"]01.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]30.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]15.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]19.06.2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]21.06.2020[/TD]
[/TR]
</tbody>[/TABLE]

So if the expiry date falls 12 months after the the current month i.e 2020
June onwards then column N should be polpulated as"Usable (>12)",


if expiry date falls between 7 - 12 months after expiry date
which is from December 2019 - May 2020 then column N should be polpulated as"Usable (7-12)" .

Those are your rules, from June onwards it must be "Usable (>12)"

Sorry, but now I'm very confused.

 

[mogss_04]

Hi Dante,

Sorry if i am confusing you. To make it simple, if i am running the code in May, May should be my base month, if i am running in June then June should be my base month and so on for the other months following and the calculations as below. Giving you example for 2 months that is May and June, would be applicable similarly for all the other months as well.


1. For Batch Expiry date and filtering by "Unrestricted use" and "Unrestricted-Use Mat".

[TABLE="width: 637"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]May File [/TD]
[TD]June File [/TD]
[/TR]
[TR]
[TD]1st June 2020 on wards - Usable (>12)[/TD]
[TD]1st July 2020 on wards - Usable (>12)[/TD]
[/TR]
[TR]
[TD]1st December 2019 - 31st May 2020 - Usable (7-12)[/TD]
[TD]1st January 2020 - 30th June 2020 - Usable (7-12)[/TD]
[/TR]
[TR]
[TD]1st May 2019 - 30th November 2019 - Near Expiry[/TD]
[TD]1st June 2019 - 31st December 2019 - Near Expiry[/TD]
[/TR]
[TR]
[TD] Before 1st May 2019 - Expired[/TD]
[TD] Before 1st June 2019 - Expired

[/TD]
[/TR]
</tbody>[/TABLE]

2. For Batch Creation date when Batch Expiry Date is "#" and Blanks.

[TABLE="width: 637"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]May File [/TD]
[TD]June File [/TD]
[/TR]
[TR]
[TD]1st June 2018 on wards - Usable (>12)[/TD]
[TD]1st July 2018 on wards - Usable (>12)[/TD]
[/TR]
[TR]
[TD]1st December 2017 - 31st May 2018 - Usable (7-12)[/TD]
[TD]1st January 2018 - 30th June 2018 - Usable (7-12)[/TD]
[/TR]
[TR]
[TD]1st May 2017 - 30th November 2017 - Near Expiry[/TD]
[TD]1st June 2017 - 31st December 2017 - Near Expiry[/TD]
[/TR]
[TR]
[TD] Before 1st May 2017 - Expired[/TD]
[TD] Before 1st June 2017 - Expired[/TD]
[/TR]
</tbody>[/TABLE]

Let me know if it still not clear.