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

Like (0) Share

Report

Posted on 27 May 2021 00:19:34 by 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?

I have the same question (0)

Summarized by AI

All responses (12)

Answers (0)

PrasadAthalye on 27 May 2021 at 01:21:13

Like (0)

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

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']

Was this reply helpful? Yes No
MadhuVadlamani 46 on 27 May 2021 at 05:48:24

Like (0)

Report

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

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?

Was this reply helpful? Yes No
PrasadAthalye on 27 May 2021 at 13:32:32

Like (0)

Report

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

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?

Was this reply helpful? Yes No
MadhuVadlamani 46 on 27 May 2021 at 15:02:18

Like (0)

Report

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

Hi Prasad,
Should we not read data from the second table before filtering?
What should be the values in the filter?

Was this reply helpful? Yes No
MadhuVadlamani 46 on 27 May 2021 at 15:43:08

Like (0)

Report

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

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.

Was this reply helpful? Yes No
MadhuVadlamani 46 on 27 May 2021 at 15:43:50

Like (0)

Report

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

Also what should the values of the filter be?

Was this reply helpful? Yes No
PrasadAthalye on 27 May 2021 at 18:34:55

Like (0)

Report

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

Hi Madhu,

Refer below

Was this reply helpful? Yes No
MadhuVadlamani 46 on 27 May 2021 at 19:28:53

Like (0)

Report

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

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.

Was this reply helpful? Yes No
PrasadAthalye on 27 May 2021 at 19:48:08

Like (0)

Report

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

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.

Was this reply helpful? Yes No
MadhuVadlamani 46 on 28 May 2021 at 10:50:46

Like (0)

Report

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

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.

Was this reply helpful? Yes No