So I have two excel files, which contains the columns ‘ID’ and ‘Amount’ in both of them. So far, I’ve developed the bot to reconcile the amounts by matching the IDs and then checking if the amount matches. Any mismatches will result in the entire row being pushed to an “Exceptions” file. However, I am now trying to develop the bot such that it is able to detect if there are extra entries under the column ‘ID’ for one report as compared to the other. (as if there is 8 row entries in the first file for the column “ID”, and only 7 row entries for the second file)
Currently, I am using the “add data row” activity to duplicate rows with mismatches to the “Exceptions” excel file. However, when I attempt to use this same method for catching extra entries, it duplicates roughly (the number of entries in file A x the number of entries in file B) into the Exceptions report.
The filtering process I am currently using is an if-else-if (Whereby if ID matches, check if amount matches. if ID does not match, duplicate row into “Exceptions” file.)
Any ideas on how to push only the extra row entry to an exceptions file, without the correct entries being duplicated multiple times as well?