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.
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.