Unanswered

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

(0) Share

Report

Posted on 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?

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (0)

PrasadAthalye on at

Like (0)

Report
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 at

Like (0)

Report

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 at

Like (0)

Report

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 at

Like (0)

Report

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 at

Like (0)

Report

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 at

Like (0)

Report

Also what should the values of the filter be?

Was this reply helpful? Yes No
PrasadAthalye on at

Like (0)

Report

Hi Madhu,

Refer below

Was this reply helpful? Yes No
MadhuVadlamani 46 on at

Like (0)

Report

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 at

Like (0)

Report

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 at

Like (0)

Report

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