cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hermanocleas
Frequent Visitor

Project: Update/cleanup shared Excel spreadsheet - cannot find a recipe for next steps....

First off, I want to thank anyone for advice or help. I am just getting started with power automate. (I've only successfully create one Flow that works reliably so far). I'm very much interested in becoming an PA wizard though but don't have a good enough grasp on the terminology, working parts and concepts that may make this journey easier to navigate. So I apologize, in advance, if this post is found to be annoying due to this. I've found that I can find recipes online that can get me close but my lack of understanding - and consequently creativity - are keeping me form finding the best route to the finish. I've never considered "programming" to be something I was very good at picking up on but I am challenging myself to learn PA and would gratefully accept any references to training resources for someone who needs more of a fundamental understanding of the concepts and working mechanisms (item vs. items? and how and when those are used, e.g.) involved in creating flows via PA. I started Office365 training but it seems to assume a basic knowledge of programming fundamentals, terminology and processes. Moving on...

 

Goal: I need to update data in a shared spreadsheet - on a daily basis - with data from an exported spreadsheet from a CRM. The exported spreadsheet is table-less.

Steps: (as I understand them to be)

   1.) create table in exported spreadsheet

   2.) find rows that do not completely match

(this is where I've gotten to and am stuck)

   3.) Update cells in rows that do match that contain additional data from the update spreadsheet.

   4.) Remove rows from the shared spreadsheet that do not match any of the rows in the update spreadsheet based on the data in a specific column.

 

Thus far, my Flow is based on this recipe: Compare 2 Excel files using Power Automate (sharepains.com) I've added two more actions after the "select" action in the recipe to actually add the unique rows to the spreadsheet as shown below. So I've gotten to the point where my flow is successfully finding unique rows and if I let my flow complete to the end it will insert those rows into the shared excel spreadsheet.  The problem is (Step 3.) it creates duplicates when some rows may match in all cells except one. So I need to find a way to have those rows merge together when they are placed into the shared spreadsheet.  I then (Step 4.) need to find rows in the shared spreadsheet and remove them if they do not match a specific cell in any of the rows from the exported spreadsheet.

 

Actions I added to the referenced recipe:

Hermanocleas_0-1628172827411.png

As I have a feeling it's highly likely is; is there a better (more simple) way of doing this in Power Automate? PA can at once seem so beautifully simple (in the case of my 1 working Flow) and in others so needlessly complex and clumsy (this project which seems like it should be more simple since I'm dealing with two excel spreadsheets). My lack of knowledge makes me wonder if there's a simpler way to accomplish this but I can not find a better recipe (or training to overcome my specific shortcomings) so far.

 

Thanks.

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.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (4,675)