Efficiency strategies for comparing two excel sheets and updating a row

[HSFO]

Hi, 

I'm building a flow where a column in 1 excel file (Sheet 1) needs to be updated based on the same column in a different excel file (Sheet 2)  matched by an ID. 

The problem that i'm having is that looping through both sheets are highly inefficient. 

In sheet 1 i have a column "Current_Status" which can either be "Final" or "Final Appproved". I have the same column in sheet 2. 

If the Current Status switches from Final to Final Approved, I need to update the column in sheet 2. 

What I have done so far is to limit the data import in 'list all rows' with an Odata filter that limits entries from sheet 1 to only "Final Approved" and entries from sheet 2 to only "Final". That will improve performance somewhat, but the double looping that I set up is still very inefficient. Ill include what the flow looks like below. I just replaced the update a row with a compose action just to see what it is currently doing. 

Does anyone have any tips or tricks that can improve my performance? Any way to avoid doing double looping of both documents? I was thinking of a limit on the loop to only look for the ID (and thus limiting the number of columns to loop through to 1), but I', not able to make it work. 

Sheet 1 can have up to 3000 rows while sheet 2 might have the same or up to 5000. 

Any help would be greatly appreciated 

[tom_riha · Accepted solution]

Hello @HSFO ,

I'd try to do that using the approach I explained yesterday here.

- List the 'Final Approved' rows from sheet1

- List the 'Approved' rows from sheet2 (rows that could be potentially updated)

- 'Select' only the unique identifiers from the 'Final Approved' rows

- 'Filter array' to keep in the 'Approved' rows only the ones that are included in the 'Final Approved'

From: Output from 'List rows...' from the sheet2, the 'Approved' rows
Where:
Output from 'Select' where you select only the unique identifiers of rows from sheet1
<contains>
item()?['uniqueIdentifierColumnInSheet2']

That should give you only the overlapping rows in the two arrays in a few seconds which you can then update.

[HSFO]

Hi, 
Thank you for your reply. Your solution works well. I ended up adding a get rows matched on a joint ID in both sheets (probably should be clear on the existence of those ID columns). With that set up I only loop sheet 2 and search sheet 1 for that specific ID.  

[tom_riha]

Hello @HSFO ,

if you're trying to use my solution, then the input of the 'Apply to each' should be the output of 'Filter array' = the array that contains only the shared row IDs. As you have it now it'll go through all the rows in the Excel file.