cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Multiethnic_IV
New Member

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... 

 

Two documents:

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.

Multiethnic_IV_0-1643926232010.png

 

 

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.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.

 

Multiethnic_IV_1-1643927654036.png

Multiethnic_IV_2-1643927678933.png

 

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.

 

This idea came from this post... Excel File Tricks with PowerAutomate - Microsoft Tech Community

 

Multiethnic_IV_3-1643927799254.png

 

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. 

 

0 REPLIES 0

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (2,956)