I am looking for ways to thin my applicable information resource. I have included two screen shot examples of information I have in front of me. I have more document resource types used in my work but I am afraid of excessively complicating my question and causing confusion if I go in too far with my initial question.
My need is to implement all of the manufactures requirements which come from the first example below, listed as the (Master Document). I pick out every applicable row in this document and add it to another document. The other document these rows get added to is an access database. This is all very tedious and was started long before I started my position. I am looking to speed up processes and remove human error.
The second screen shot is an effectivity document which lists all of our applicable items (listed in top row) and was given to us by the manufacturer, and gets updated as things change.
Here is what I am asking how to do.
How do I thin the Master Document down to necessary applicable items by filtering in the information of the second effectivity document?
Master Document column F lists the requirements of applicablilty. For this topic I show "Pre xxxx" and "Post xxxx" requirements. Row 307 shows PRE 3351 or PRE 3964 or PRE 4044 etc. Row 308 shows POST 3351, POST 3964, POST4044 etc. The first example shows an "or" but the second example is an "and" where every item needs to be true for the whole line to be applicable. The manufacturer only splits the pre and post by "or".
Second document column A lists the pre/post xxxx discussed in last paragraph, however now I have 5 digits. In the second document, PROD and RETRO = POST. PART, VALID, and blank cells = PRE. confused yet? I spend weeks with 3 to 6 of these documents up and my eyes cross :( Human error is my concern.
I would like the output to result in my access database file, but I can work towards that slowly. The common thing across all my company documents is currently the info from column E of document 1, though I am working to change that common element to be the information (task reference) in Column A of the first example.
Do I need to focus on equations? Formulas? Functions? Something else? A combination of each?