Answered

Help Needed with Comparing Consecutive Rows of Excel in Power Automate Desktop using For each loop

(0) Share

Report

Posted on by Sura05

Hello everyone

I hope you're doing well. I'm reaching out because I'm facing a challenge with automating a process in Power Automate Desktop involving the comparison of consecutive rows in an Excel workbook.

Scenario:

I have an Excel workbook containing ordering data, and I want to automate the process of ordering products from a supplier website by comparing the postal code and address between consecutive rows to identify if both items are ordered by the same person. I aim to order items in bulk until I encounter a different postal code or address. If the postal code and address between consecutive rows are different, I will order items one by one, assuming they are ordered by different people.

Challenge:

While I've managed to set up the basic structure, I'm struggling with the implementation details, particularly regarding:

Dynamically reading data from the current row and the next row within the "For Each" loop.

Specific Questions:

How can I dynamically read data from the current row and the next row within the "For Each" loop in Power Automate Desktop?
What is the best approach for comparing data between consecutive rows in Excel using Power Automate Desktop?
How can I update rows in Excel based on the comparison result within the automation?

I would greatly appreciate any guidance, tips, or sample workflows that can help me overcome this challenge. Thank you for your assistance!

Best regards,
Suraj

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (2)

Sort by

Verified answer

Deenuji_Loganathan_ 6,255 Moderator on at

Like (1)

Report
Copy link

Link copied!
@Sura05

Please follow the flow as suggested below.

My excel file looks like below:

Code:
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\deenu\\Order_Details.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> OrderExcelData SET CustomerName TO $'''%''%''' SET CustomerPincode TO $'''%''%''' SET CustomerAddress TO $'''%''%''' LOOP FOREACH DataRow IN OrderExcelData IF (CustomerName = DataRow[4] AND CustomerPincode = DataRow[2] AND CustomerAddress = DataRow[3]) = $'''True''' THEN Display.ShowMessageDialog.ShowMessage Title: $'''info''' Message: $'''matching''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed END SET CustomerName TO DataRow['CustomerName'] SET CustomerPincode TO DataRow['Pincode'] SET CustomerAddress TO DataRow['Address'] END

How to copy/paste above code into your PAD?

In case if you have any queries, pls let me know

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀. If you'd like to appreciate me, please write a LinkedIn recommendation 🙏

Was this reply helpful? Yes No
Sura05 8 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your kind assistance.
But it's not exactly what I want to do.
My Excel looks like this. Currently, I am in Japan, so my Excel data contains Japanese letters. Please ignore them. I have translated the necessary data into English.

What I really want to do is as follows:
1. First, compare the postal code, Address 3, and customer name of the first row with those of the second row. If all the fields in both rows are the same, I want to order both the first and second items together. In the Excel spreadsheet, the fields of the first and second rows are the same.
But while comparing first and second row, if both have different field, then want to order first row item first and then second item after first.
2. Similarly, in the second iteration, compare the second and third rows. This time, both rows have different fields. So, I want to order the items from the third row only because the second row is already ordered in the first iteration.
3. In the third iteration, compare the third and fourth rows, and so on, until reaching the last row."

Was this reply helpful? Yes No
Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

I would recommend using a simple Loop action instead of a For each in cases like this. Loop from 0 to %ExcelData.RowsCount-1% will do the same as For each, but will generate an index instead of a %CurrentItem%. You can then reference the rows via this index. So, your current row will be %ExcelData[LoopIndex]% and the next one will be %ExcelData[LoopIndex+1]%, etc.

You might then want to do a nested loop inside your loop to continue incrementing the index until you find the row that no longer matches, and then continue.

Was this reply helpful? Yes No