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

Like (0) Share

Report

Posted on 13 Mar 2024 14:11:09 by Linardo

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:

Result :

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

Excel A.xlsx

Excel B.xlsx

I have the same question (0)

All responses (4)

Answers (0)

Linardo 8 on 19 Mar 2024 at 12:16:37

Like (0)

Report

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

Thank you, i will do that.
Regards

Was this reply helpful? Yes No
Deenuji_Loganathan_ 6,206 Super User 2025 Season 2 on 18 Mar 2024 at 17:38:27

Like (0)

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

If column B = "e-Remito" then delete row
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?

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
Linardo 8 on 18 Mar 2024 at 13:26:02

Like (0)

Report

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

Was this reply helpful? Yes No
Riyaz_riz11 3,656 Super User 2025 Season 2 on 18 Mar 2024 at 04:30:46

Like (1)

Report
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

Was this reply helpful? Yes No