Thanks a lot, Vinay and Jacob! Let me explain the scenario. Please find attached the template.Template.xlsx (9.7 KB)
This is a template of vendors who would be shipping to various states mentioned. One vendor can ship to multiple states and different states under one vendor may have different POCs as shown in the template.
There is a master sheet that includes n number of such vendors shipping to states across India. We need to use the data in this template to filter that master sheet based on the ID and State and then send consolidated data to the POC e-mails. For eg. ABC (1001) has no state assigned which means data for all states for ABC goes to one single POC. GHI (1002) has one POC for 3 states (MH, TN and AP) and a different POC for WB and MP. 1003 and 1004 have one POC each and they ship to one state only.
(a) We need to ensure we send the correctly filtered data to the respective POC.
(b) We are saving the filtered data to separate excel files and then sending the files to the POCs.
(b) A POC should receive only one e-mail with consolidated data for whatever ID(s) and state(s) is/are allocated to him.
© Vendor-State combinations not assigned to any POCs (i.e. not appearing in the template excel but are there in the master sheet) go to a single separate email (say firstname.lastname@example.org) for additional research on who the POC for those would be.
How can we achieve this ?
Greatly appreciate your help.