Compare 2 Excel Files and Add amounts if ID is the same

Help Studio
, ,
1

Hi,

I am trying to compare 2 excel files that have entries with same ID number. If the ID number is a match I want to subtract the Amount 2 (1st sheet from example) from Amount 2 (2nd Sheet in example). The 2nd sheet has the ID numbers but it has a name after that,

Example 1
|ID | Amount1 |Amount2|
|12345 | $100.00 | $250.00 |
|43213 | $110.00 | $269.00 |
|343124| $120.00 | $288.00 |
|545421| $130.00 | $307.00 |

Example2
|ID |Amount1 |Amount2|
|12345-Name | $1,081.00 | $2,020.00 |
|43213-Name21# | $1,034.00 | $2,054.00 |
|343124-Name%31 | $987.00 | $2,088.00 |
|545421-Name | $940.00 | $2,122.00 |

2

Hi,

Here is a pseudo-code to achieve that:

image
image648Ă—673 14.5 KB

To match the IDs you can simply compare if "12345-Name".StartsWith("12345-") as in the screenshot.

3

Thank you for the response. I am new to UiPath and not completely sure how it will continue to subtract the Amount2 row1 from Amount 2 row2 and write it in a new excel file with row2 ID

4

Hi @Nstojkov

You can use in Assign:

row2("Amount2") = Decimal.Parse(row2("Amount2").ToString) - Decimal.Parse(row1("Amount2").ToString)
5

It doesn’t seem to work. Not sure if this makes a difference but the ID’s on both reports are not in the same rows .

6

I’ve attached the workflow and excel files.
I am trying to compare those 2 excels

  1. if Clear Value from workbook matches any value from “Entity Name” (First numbers), not in order, then
    Subtract “Adjusted Balance” Workbook1 from “Market Value Pledged” Workbook2 and write it in a separate New excel with “Entity Name” on Workbook2.
    If Clear Value Can’t be found then it can give a message

The above workflow didn’t work

Compare_Results1.xaml (8.2 KB)
Workbook1.xlsx (12.6 KB) Workbook2.xlsx (9.7 KB)

7

Hi @Nstojkov

The changes occur in memory, you need write the results back to excel.

image
image1071Ă—660 32.6 KB

8

Thank you very much that works but the end result is not correct. Seems like because the “Adjusted Balance” has a comma (,) in the number to Sum is not correct.

9

Hi @Nstojkov

You can change the format provider based on your region/culture.

E.g, for Portuguese Brazil “.” is a thousand separator and “,” is decimal separator.:

 Decimal.Parse(row("value").ToString, System.Globalization.CultureInfo.CreateSpecificCulture("pt-BR"))

Please, consult CultureInfo Class (System.Globalization) | Microsoft Learn for more info.

10

I have one more request. How can do one more assign where if the row value is <25000 to delete the whole row

11

Inside the For Each add an If activity and check if the value is < 25000 and then use Remove Data Row activity:

Pseudo-code:

if (Decimal.Parse(row("YouColumn").ToString, CultureInfoHere) < 25000 {
     Remove Data Row # passing here the row and the data table in the arguments
}
1 Like
12

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