Reading Mails - Excel Filters and Sending Teams Message

[raash]

Hello Guys, 

Very new to power automate and I am amazed of its endless possibilities but I am stuck on this:

a) Can we set up a flow to automate this, every week once an email comes in we need to compare 2 excel files, one from the existing mail and other from another mail (could put both on SharePoint, but the trigger is the mail with a certain subject) and then use filters to see against a specific category on what is missing from the second file and then send a note on Teams to the user with the list of missing rows? If also possible, once comparing - can we see what is not available and match the user name from the excel column and send respective users what is missing of their tasks and send teams to each of them?

b) Secondly can we also set up a flow where it reads incoming mails and once it notes a specific 5 digit number in the email, the flow should search back on an excel to see who handles that 5 digit number and intimate the specific person about an email?

Also, does Excel comparison have a limit of rows?

@Pstork1 @ScottShearer @ekarim2020 @tom_riha @fchopo @MichaelAnnis - do you think any of you can help? Could explain more.

[MichaelAnnis]

There is a lot to unpack here, and this is going to be pretty intense if you are new to Power Automate Desktop, but you can do most of what you are talking about.  

The one thing you can't do is trigger PAD by an email receipt.  You can trigger Power Automate (the cloud/flow version) from E-mail, and that can call PAD, but you can't trigger PAD without any extra help (human, power automate, bot scheduler, etc.)

The rest of it seems pretty straight forward.  As far as comparing in Excel, are the documents essentially exactly the same with missing inside information?  1) all the row headers are static and don't move 2) the row ID's are static and don't move - no one is deleting rows?  Either way, I would do this in an Excel Macro rather than Power Automate Desktop, as it is going to be much faster. 

If the two spreadsheets are static (no rows/column moving and every row and column lines up between the two), you could simply create a 3rd sheet, and say =IF(EXACT(Sheet1!A1,Sheet2!A1),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"ERROR") which says:

• If they match, use the first sheet (the if(isblank) is just so it doesn't show up as a 0 if blank)
• If they don't match, put "ERROR" (you could change this with whatever you want)
• Copy this formula to the right and down for all cells

If rows are moving around, being deleted, or added, then I would copy the row identifiers from both sheets, pasted subsequently into the 3rd Sheet, then remove duplicates, and then you could use Index/Match on both to do the same logic:

• =IF(EXACT(INDEX(Sheet1!$A$1:$P$38,MATCH($A2,Sheet1!$A$1:$A$38,0),MATCH(B$1,Sheet1!$A$1:$P$1,0)),INDEX(Sheet2!$A$1:$P$38,MATCH($A2,Sheet2!$A$1:$A$38,0),MATCH(B$1,Sheet2!$A$1:$P$1,0))),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"ERROR")
• So, this would be the formula in B2 where titles are in row 1 and row IDs are in Column A.  If your columns don't move, you could also do vlookup, but Index Match will always work if people are moving/deleting columns and rows.

Good luck!

[raash]

@MichaelAnnis I am not sure if you came with what I expected. I can share you the Flows I am expecting. I am looking for 2 flows. I could make sure the excel headers match.

FLOW 1:

Flow 2:

:

[MichaelAnnis]

Oh, that’s much easier. I can get into details now, but I’ll hit you up in the AM if VJR doesn’t beat me to it 🙂

[raash]

Oh, that is wonderful news. Thanks a lot, much appreciated. Also, this is just a template of the data I will be handling. Do you think there will be limitation in Excel File Rows? If yes, is there a way to overcome that? I might have over 20,000 Data Points and it should search/scan through all for both flows.

[MichaelAnnis]

Okay, sorry for the delay.  From what I understand, you cannot remove/delete from a datatable using the canned PAD actions.  Here is what I have to get the list of Countries that are remaining (List1 - List2):

With that said, you still have some major hurdles in front of you.  You need to get the countries listed back into Excel (below), but then you have to lookup the Continent code.  Earlier you said 20K rows (a little confusing because there is only 181 countries), but that is not something you want to filter through under a for each scenario.  What would be better would be using Excel VBA or writing a Vlookup formula on your new worksheet to lookup the continents based off the first datatable.

Once you have that, come back, and we can hash out the emails.

[MichaelAnnis]

@raash ,  my apologies.  I did not realize this was on the Power Automate (online/flows) forum.  I deal strictly with PAD, and all the questions relating to PAD come through my email.  I only saw this one because you tagged me in it.  If you are looking for a flow answer, unfortunately, I do not have the experience with flows to direct you in the right way.