Creating 37 different excel files from two different files
Bottom line up front: I am trying to take two documents and filter one then join it to the other, finally saving it as a new file. I've gone around my elbow to try and figure this out...
1. Base document - This is an excel file that will be used by 37 different individuals, they will fill it out and it will live in SharePoint. This file has four tabs/worksheets. The last worksheet needs to be populated by document two. Currently, there is only one base document. The hope is to fill the data worksheet and then copy it with a new name in a new location and then iterate to the next file. (Apply to all will be used) Once they initial that they are done, I will have it trigger the admin who will await all 37 to be complete before moving forward. (This is not an approval back and forth project, it requires the individual to get in and come back to the document until they make their final decisions)
These are the worksheets on the base tab... "Data" is the one I need to update.
2. Reference document - This excel file has all of the data that is required for the final worksheet for each of the end products. I am trying to filter it by "ME 1% Group" and then fill the base document with the data that is available. There are 37 different ME 1% Groups in the reference document...
This is screen shot of the reference document.
This is the reference document I need to make into an array, filter, and add to the base document over and over until I have 37 different documents with the same base document and data from the right group.
Ways I've tried to solve it...
1. I made my excel reference document into a Microsoft List so that it was already easily available to use but it gives me errors when I setup my apply to each.
2. I've gone other routes looking at posts from other people... I ended up making complex flows as I tried to make my data into an array and then filter array to post into my data tab.
I appreciate any help anyone can give me in getting me to the end solution.
For bonus what I really want to do is to use a third reference document and add rows to tables in each of the 37 documents on the "New" and "Veteran" tabs. It was just getting too complicated for me so I am leaving that out as of now.