Create a macro/program in Google Sheets

Bin Numbers - We’ll be inputting an RX BIN + possibly PCN + RX Group This is how we want the program/macro to work. User will be inputting data in a completely separate google sheet than where the data is pulling from. In that completely separate google sheet, the data will also be output. Input/Output sheet is [login to view URL] Data for all the steps below is in [login to view URL] Step 1) We’ll input a BIN #, a possible PCN alphanumeric, and possible RX Group alphanumeric. Data for these fields are located in the “Bin Numbers” tab. That will spit out what is in Column A of “Bin Numbers” Tab. Step 2) Column A will then be used to match with columns B, D, F, H, J, L, etc. in the “Data” tab Step 3) From there, It will go through the “Data” tab and will show which Drugs (RX column) are covered (indicated by letter “Y”) and which drugs are indicated by “PA”. Need two types of outputs from here. User will select from list of Disease States listed in Column A of “Disease States” tab. User will also have an option to select “Unknown” as a disease state. If user selects “Unknown” then proceed to step 4. If user selects a disease state, skip Step 4 and go to Step 5. Step 4) We want to show all these drugs that are tied to the RX BIN + possible PCN + RX Group. We want to show them split by “Y” and “PA”. All “PA” drugs that will be listed need to include their corresponding PA notes from the “PA notes” column in the “Pricing/Package Size” tab. So in all we should be able to see drugs split by “Y” individually and also by “PA” individually. Those PA drugs will need to show the corresponding “PA notes.” Step 4a) Take the amount paid (“Data” tab) for each of those drugs that came from Step 4 and subtract it from “Acquisition Price” in Pricing/Package Size tab. Use this to sort those drugs that were pulled in step 4 and sort from highest to lowest. Step 4b) Show those drugs, sorted highest to lowest. Step 5) We want to show all drugs tied to the selected disease state. These will be tied to the RX BIN + possible PCN + RX Group first inputted. We want to show them split by “Y” and “PA”. All “PA” drugs that will be listed need to include their corresponding PA notes from the “PA notes” column in the “Pricing/Package Size” tab. So in all we should be able to see drugs split by “Y” individually and also by “PA” individually. Those PA drugs will need to show the corresponding “PA notes.” Step 5a) Take the amount paid (“Data” tab) for each of those drugs that came from Step 5 and subtract it from the “Acquisition Price” in Pricing/Package Size tab. Use this to sort those drugs that were pulled in step 5 and sort from highest to lowest. Step 5b) Show those drugs, sorted highest to lowest.