web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : h6TpBIv8bNcSmFvLTUYWFZ
Power Automate - Power Automate Desktop
Unanswered

Help - Reconcile 2 Excel files and output the differences into a new 2 Excel file

Like (0) ShareShare
ReportReport
Posted on 13 Mar 2024 14:11:09 by 8

Hello Community,

 

I need your help to reconcile 2 excel files using Power Automate Desktop and output the differences of each file into a 2 new excel files.

 

First i need to depure "Excel A" (Attached)

- First 9 rows can be deleted

- If column B = "e-Remito" then delete row

- If column B = "Nota de Crédito de e-Factura" then i need to find if there is another row with column B = "e-Factura" with the same "RID"(Column E) & "Cur"(Column F) and "Net""Tax""Total" (Column G,H,I) need to be the same positive number instead of negative. In that case delete both rows.

For example:

Linardo_0-1710337666932.png

Result :

Linardo_1-1710337734929.png

 

 Now i need to reconcile files.

- For each row in "Excel A"

If [Excel A] "Column A" & "Column F" & Column H" & Column i" match with [Excel B] "Column E" & "Column H" & "Column i" & "Column J" then matched rows in both excel files should be deleted.

 

Now Excel A and Excel B can be saved as new file named "Reconciled Excel A" and "Reconciled Excel B" only with the rows that dont have a match between both files.

 

Thank you in advice

  • Linardo Profile Picture
    8 on 19 Mar 2024 at 12:16:37
    Re: Help - Reconcile 2 Excel files and output the differences into a new 2 Excel file

    Thank you, i will do that.

    Regards

  • Deenuji_Loganathan_ Profile Picture
    6,204 Super User 2025 Season 2 on 18 Mar 2024 at 17:38:27
    Re: Help - Reconcile 2 Excel files and output the differences into a new 2 Excel file

    Hi @Linardo,

    I understand your concern, but the community members are here to assist if you encounter any difficulties while attempting tasks. Additionally, breaking down your tasks one by one allows for a more manageable approach, fostering both learning and support from the community.

     

    That being said, I'm here to guide you through deleting the first 9 rows from the Excel file.

    Please follow the provided workflow and code below.

     

    Deenuji_0-1710783025300.png

    If column B = "e-Remito" then delete row

    Deenuji_1-1710783040506.png

    Code[Copy and paste the below code into your PAD it should work]:

     

     

    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\deenu\\Downloads\\Excel A.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
    Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet0'''
    SET RowCounter TO 1
    LOOP WHILE (RowCounter) <= (9)
     Excel.DeleteRow Instance: ExcelInstance Index: 1
     Variables.IncreaseVariable Value: RowCounter IncrementValue: 1
    END
    Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
    LOOP FOREACH CurrentItem IN ExcelData
     IF CurrentItem['Type'] = $'''e-Remito''' THEN
     # Delete you rows
     END
    END

     

     

     

    How to copy and paste the above code?

     

    Deenuji_2-1710783448739.gif

     


    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 🙏

     
  • Linardo Profile Picture
    8 on 18 Mar 2024 at 13:26:02
    Re: Help - Reconcile 2 Excel files and output the differences into a new 2 Excel file

    Hello, Thank you for your answer Riyaz.

    I cant use excel as database, I need to find a way to use power automate desktop only but logic and the conditions are overwhelming me to do it, I don't have much experience with this tool but I find it difficult

  • Riyaz_riz11 Profile Picture
    3,569 Super User 2025 Season 2 on 18 Mar 2024 at 04:30:46
    Re: Help - Reconcile 2 Excel files and output the differences into a new 2 Excel file

    Hi @Linardo ,

    Better you can try with excel as a database.

    @@copilotGeneratedAction: 'False'
    Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%strDataFolderPath%\\BusinessConfig.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";''' Statement: $'''Delete FROM [Config$]
    where [Type]=\'e-Remito\'''' Timeout: 30
    @@copilotGeneratedAction: 'False'
    Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%strDataFolderPath%\\BusinessConfig.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";''' Statement: $'''SELECT name FROM [Sheet1$]
    where [Type]=\'Nota de Crédito de e-Factura\'''' Timeout: 30 Result=> tblExcelData
    

     

    For excel as database you may need to install oledb driver 64bit.

    For 2nd execute you can try with nested select query and get the values.

     

    If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

    Regards,
    Riyaz

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2