Compare and Multiply 2 columns from 2 different excel files

Help Activities
, ,
1

Hello ,

In my query I would like to compare column (A) from Excel file (A) to Column (B) from Excel file (B) and If the condition is met I want to multiply the Column (A) into Column (B) and write range in Excel file (B)

1 Like
2

hi @Yugal_Raju

first you have to read both the excel files using read range activity and create a datatable variable in output tab of read range activity like DT1,DT2

use for each activity row to iterate DT1
inside that for each activity use another for each row activity to iterate DT2

inside the second for each activity Use if condition to check like Convert.ToDouble(( Currentrow1(“Column A”).ToString)=Convert.ToDouble(Currentrow2(“Column 2”).ToString))

inside that if activity

use assign activity to multiply
Currentrow2(“Column name”) = Convert.ToDouble(Currentrow1(“Column A”).ToString)=Convert.ToDouble(Currentrow2(“Column 2”).ToString))

finally use write range to write activity to write data into excel( put it outside the for each row activity)

Hope it solves your issue

Thanks
Robin

3

Hi

Welcome to uipath forum

Hope the below steps would help you resolve this

  1. Use a excel application scope and pass filepath as input

  2. Inside the scope use a READ RANGE activity and get the output as datatable dt1

  3. In the same way use another excel application scope and read range and get the output as dt2

  4. Now use a FOR EACH ROW activity and pass dt2 as input and change the variable name from CurrentRow to row1

  5. inside that loop use another FOR EACH ROW activity and pass dt1 as input and change the variable name CurrentRow to row2

  6. Inside the inner for each row loop use a IF condition like this

row2(“Columnname-A”).ToString.Contains(row1(“columnname-B”.ToString)

If true it goes to THEN block where use a assign activity like this

row2(“ColumnName-B”) = ( Convert.ToDouble(row2(“ColumnName-B”).ToString) * Convert.ToDouble(row1(“ColumnName-A”).ToString) ).ToString

And inside the same THEN part next to the above assign activity use a BREAK ACTIVITY to get out of the loop once match is done

  1. Now outside the loop use a write range activity and pass dt2 as input and enable add headers and mention the sheet where to write the output datatable

Cheers @Yugal_Raju

1 Like
4

In the assign activity to multiply I’m getting
Error " Assign : Input string was not in a correct format"
The values in both columns are mix of decimals and whole numbers

5

Can you share your input excel

6

Fine
No worries

Is there any - symbol along the value

Try with this to avoid space at last

row2(“ColumnName-B”) = ( Convert.ToDouble(row2(“ColumnName-B”).ToString.Trim) * Convert.ToDouble(row1(“ColumnName-A”).ToString.Trim) ).ToString

Cheers @Yugal_Raju

7

Yes the error is resolved.
It is working thank you

8

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