Hi All, I’m trying to develop an automation where I’m comparing 2 files - File 1 and File 2. File 1 has a list of transactions, File 2 has the same list - plus new transactions. They are formatted the same way. I need to find the last matching row based on 3 out of 4 columns, and then only copy the newest rows from File2 to File1.
Currently I’m finding the last value in each column and saving it to a variable in File1 then have in If Statement for File2- If each column is equal to the variables of the last row in File2, then Set another variable to CurrentIndex.
Then for each row in File 1 if CurrentIndex>IndexofLastRow write each cell. However, instead it’s writing each row and resulting in duplicates
I can share test files. See attached. File ‘Test’ has a few transactions, File ‘Test2’ has the same transactions, but also new ones. I need the robot to find the last row on ‘Test’, find the matching row based on Columns A, C & D on ‘Test2’ and add any following rows from ‘Test2’ to ‘Test’. Currently, the bot adds ALL lines from ‘Test2’ to ‘Test’ resulting in duplicates. Test.xlsx (9.2 KB) Test2.xlsx (9.3 KB)
I can share test files. See attached. File ‘Test’ has a few transactions, File ‘Test2’ has the same transactions, but also new ones. I need the robot to find the last row on ‘Test’, find the matching row based on Columns A, C & D on ‘Test2’ and add any following rows from ‘Test2’ to ‘Test’. Currently, the bot adds ALL lines from ‘Test2’ to ‘Test’ resulting in duplicates.
Output: dt_Test2
=> Use the below code in Invoke Code activity:
' Get the last row from 'Test'
Dim lastRowTest As DataRow = Test.AsEnumerable().LastOrDefault()
' Iterate through each row in 'Test2'
For Each rowTest2 As DataRow In Test2.Rows
' Check for matching rows in Columns A, C, and D
Dim matchingRows = Test.AsEnumerable().Where(
Function(x) x("Date").ToString() = rowTest2("Date").ToString() AndAlso
x("Amount").ToString() = rowTest2("Amount").ToString() AndAlso
x("Code").ToString() = rowTest2("Code").ToString()
)
' If no matching rows found, add the row from 'Test2' to 'Test'
If matchingRows.Count() = 0 Then
Dim newRow As DataRow = Test.NewRow()
newRow("Date") = rowTest2("Date")
newRow("Amount") = rowTest2("Amount")
newRow("Code") = rowTest2("Code")
Test.Rows.Add(newRow)
End If
Next
