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

[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:

1. How can I dynamically read data from the current row and the next row within the "For Each" loop in Power Automate Desktop?
2. What is the best approach for comparing data between consecutive rows in Excel using Power Automate Desktop?
3. 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

[Deenuji · Accepted solution]

@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 🙏

[Sura05]

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."

[Agnius · Accepted solution]

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.