best method for automating a process of checking against a master file?

[donald83]

Hi all

just a question around what would be the best course of action to get the below process automated. would it be a Macro/VBN? just after some guidance and to get pointed in the right direction then I can engage with someone to do the work.

Once a week we receive a SOH report which shows product qty's and best before dates which we then have to manually go though by line and see if anything is coming up close to best before date. (small example attached) what I am trying to do is if we have a master workbook (second sheet tab1) with the minimum best before for each product code can something be setup to automatically check all the codes and best before dates and return them in a way that shows things that are within 30, 60 , 90 , 120 days of minimum best before. (example of returned values in second sheet tab2)

this way we can easily run it and see what items are coming up on best before date.

thanks

[JeteMc]

First I would suggest putting the SOH report in the master data workbook. As you are using Excel 365 you could utilize Get & Transform (Power Query) for this.
A formula based way to accomplish the table on the Return Information sheet would be to use the following:
1. For the Codes:

Please Login or Register  to view this content.

2. For the quantities:

Please Login or Register  to view this content.

Note that the values in row 10 are obtained using: =VALUE(LEFT(B11,SEARCH(" ",B11)-1)) and may be hidden by choosing white font for those cells (for aesthetic purposes)
Note that the date in cell A10 could be populated using: =TODAY() although that would produce all blank cells with the dates that were provided in the SOH example
Let us know if you have any questions.