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
Thank you, i will do that.
Regards
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 🙏
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
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
Michael E. Gernaey
497
Super User 2025 Season 2
David_MA
436
Super User 2025 Season 2
Riyaz_riz11
244
Super User 2025 Season 2