Excel Vlookup in Uipath with substracting column

[kawtar.ettayarssouti]

i have 2 excel datatables dt1 and dt2
dt1 columns : Bank name ; Total Amount
dt2 columns : Bank name ; Total amount

i want for every Bank in dt1 to look at dt2 and once a bank name figures in dt2 i want that it calculcates the difference of total amount :
Dt1(Total Amount) - Dt2(Total Amount)
and then put the result in a new column added to D1 called “Ecart”

i tried to use the Vlookup excel function in UiPath but i found problems in configuration and how to put the result in another column (ecart)

[arjunshenoy]

Hi @kawtar.ettayarssouti, welcome to the Community.

Please try the following query in Invoke Code:

(From row1 In dt1.AsEnumerable()
 Join row2 In dt2.AsEnumerable()
 On row1("Bank name") Equals row2("Bank name")
 Select row1("Bank name"), Ecart = row1("Total Amount") - row2("Total Amount")
 Into dtResult
 Select dtResult).CopyToDataTable()

Hope this helps,
Best Regards.

[supermanPunch (Arpan)]

@kawtar.ettayarssouti

i have 2 excel datatables dt1 and dt2
dt1 columns : Bank name ; Total Amount
dt2 columns : Bank name ; Total amount

Hi @kawtar.ettayarssouti ,

Have you tried to use Join Datatables activity and with Join Type as Left Join and DT1 as the First Datatable.

After the Join operation is performed, we will have the resultant datatable with two Total Amount columns (Total Amount and Total Amount_1)

Then to calculate the difference and update in another column, we could use Add Data Column activity to add the new column. Loop through the data using the For Each Row activity and update the column values.

Another way to update would be to use DataColumn Expression.

Let us know if you are able to perform the above steps or if do find any difficulties in performing it.

[kawtar.ettayarssouti]

Main2.xaml: No compiled code to run
error BC30035: Syntax error. At line 1
error BC30800: Method arguments must be enclosed in parentheses. At line 2
error BC32017: Valid comma, ‘)’ or continuation of expression expected. At line 2
error BC30817: 'The ‘On GoTo’ and ‘On GoSub’ instructions are no longer supported. At line 3
error BC30095: ‘Select Case’ must end with a matching ‘End Select’. At line 4
error BC30205: Expected end of statement. At line 4
error BC30058: Invalid statements and labels between ‘Select Case’ and first occurrence of ‘Case’. At line 5
error BC30800: Method arguments must be enclosed in parentheses. At line 5
error BC30095: ‘Select Case’ must end with a matching ‘End Select’. At line 6
error BC30205: Expected end of statement. At line 6
error BC30451: ‘row2’ is not declared. It may be inaccessible due to its level of protection. At line 2
error BC30132: The label ‘’ is not defined. At line 3
error BC30451: ‘row1’ is not declared. It may be inaccessible due to its level of protection. At line 4

[kawtar.ettayarssouti]

i will try this method and see if it works