Excel Reconciliation- Removing matched transactions and retaining Unmatched transactions

Help Studio
, ,
1

Hi Community!!

Please, I need help carrying out Excel reconciliation.

I want to locate each unique ID and its corresponding Amount on the Settlement report, match it off, and remove them using the same unique ID and Amount on the statement report. After all the reconciliations, I want to be left with only unmatched transactions on both reports.

2

HI @Lamina_Zafrullah

can you share the sample excel

Regards

3

Settlement Report.xlsx (9.1 KB)
Statement Report.xlsx (9.1 KB)

Okay @vrdabberu . These are the sample excel files for your reference.

Thanks

4

@Lamina_Zafrullah

Easier way would be to use join activity …please follow below steps

  1. Read first report into dt1 and second into dt2
  2. Now use join datatable activity with join type as outer and columns as ID and amount from both tables
  3. Now use a filter datatable on the joineddt and filter on empty id and empty amount for the dt1 related columns and keep those rows which are the non matched rows for dt2
  4. Repeat same filter but now for dt2 related columns from joineddt and keep those rows which are the non matched rows in dt1

Happy to help if you face any issues

Hope this helps

Cheers

5
  1. Read both the excels in two datatables dt1 and dt2.
  2. Use a left outer join where the left table will be the excel sheet whose data you want to be kept in end.
  3. Then get the amount of matched data by checking if unique ID_1 is not empty (if amount is needed of matched reports)
  4. Filter the joined datable by removing rows if unique ID_1 is not empty and keeping the columns you need
  5. Do the join in reverse if you want the other datatable details
6

@Anil_G

Can you please be a bit more elaborate? Can’t find outer join in UiPath join activity. Is it possible for you to please send a screenshot of the whole process so I can understand better, please?

Thank you

7

@Lamina_Zafrullah

My bad…I should have said full instead of outer

Here is a sample

Cheers

8

@Anil_G Thanks, it worked.

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.