Looking to update a column in an excel sheet based on a row of data from another excel sheet

[MadhuVadlamani]

Hello,

I am new to PowerAutomate. I am looking to use Powerautomate to create a flow that will match 2 columns(CustomerID) in 2 different Excel workbooks and update a column in Excel Workbook 2 based on the matching values in Excel workbook 1. Can anybody please help?

[PrasadAthalye]

Please refer a high level solution below...

• Create a table for the data within both the excel.
• Read table from first excel 1 using List Rows Present in Excel
• Use Filter Array to filter a row to match the columns that you are looking for.
• Use Update a Row on 2nd Excel. Provide Key from 2nd Excel and value from Filter Array.

For Value, you can use expression like below where Apply to each is your loop name and Test1 is column in excel 1.

items('Apply_to_each_7')?['Test1']

[MadhuVadlamani]

Thank you Prasad.

Is there no need to list the values from the second table?

Also the 2 columns I am trying to match between the 2 excel workbooks are in Text format, how do I match them?

Please look at the screenshot

1. I listed rows of first excel

2. Apply to each on first

3 Used a nested loop and listed rows of second excel 

4 then apply each on second excel

5. Applied a condition to check if the Customer ID from both excel are equal( both are text, how to check if they are equal)??

6 I am trying to update a column in the excel 2 based on the matching IDs.

It is always failing. Could you tell me why this doesnot work? 

[PrasadAthalye]

Reason you dont have to Get rows from 2nd table is because you are trying to update. Within update, you can specify key and value to uniquely identify row and update it.

Can  you upload flow run that has error and explain which Action failed?

[MadhuVadlamani]

Hi Prasad,

Should we not read data from the second table before filtering?

What should be the values in the filter?

[MadhuVadlamani]

Hi Prasad,

So I have 2 excel sheets, A and B and both have data already.

I want to use powerautomate and take the values of a column called "Health indicator' in Excel A and paste it into a column called "RFX" in Excel B. My matching criteria is Customer ID which is present in Excel A and Excel B.

Could you give me the detailed steps?

Also I created a nested loop without filters for this requirement but the flow is taking too long to execute.PFA the scrreenshot.

[MadhuVadlamani]

Also what should the values of the filter be?

[PrasadAthalye]

Hi Madhu,

Refer below

[MadhuVadlamani]

Thank you Prasad.

Will this flow match the Customer IDs between both the excel sheets?

Only the Customer Ids match and I have to update a different column in the 2nd excel corresponding to the matched Customer ID.

Please excuse my ignorance.

[PrasadAthalye]

Yes. Key Column from 2nd Excel for you should be Customer ID. Then Value should be Customer ID from first Sheet.

Please try and the let me know if it works.

[MadhuVadlamani]

Hello Prasad,

My condition is failing. The customer ID is a text field and not numbers. The equal to function is not working. 

Could you help me with the right syntax for checking if the 2  customer IDs( which are in text format) are equal ?

Thanks again.

[MadhuVadlamani]

Hello Prasad, I tried this but we do not have any condition to check if the Customer Ids from the excel sheets are matching, right? I am getting an error for the non matched IDs

[MadhuVadlamani]

Hello Prasad,

Thank you so much for your help. It worked. I have some more improvements to make over these flows for which I am looking for help. Could you please help me?

I have created a nested loop such that we go through the customer IDs of the first excel file and start matching it with the customer Ids in the second Excel file.

 Whenever a match is found, a row called Health Indicator must be updated in the second excel file based on the value coming from the Health Indicator column in the first excel sheet for the record where the Customer Ids match.

I am able to get this. My concern now is as follows:

1. Whenever a match between the Customer IDs is found, we need to break out of the inner loop and stop the Customer ID(from first excel file) to continue matching with more customer Ids in the second excel sheet. How do I do that?

2. When there is a no match, I need to store both the Customer IDs in some place and then finally send an email with those values after all the loops are executed.