How to pass value to another column

Data1 has 9 columns and Data2 has 6 columns
I need to add Column7 and Column8 to Data2 on below given condition

The 3 conditions are
A. column3 (MRC) of Data2 should be multiplied with column4 (TAX_RATE) of Data1 if column3 (TAX_TYPE) of Data1 matches to column4 (TAX) of Data2. (to be added to column7 of data2)

B. If the column4 (TAX_RATE) is 0 in data1 then do not multiply with MRC of data2 but pass the value of column5 (TAX_FIXED_AMOUNT) of data1 to Column7 of Data2

C If column7 (after adding to data2) of data2 does match with column6 (Percent) of data2 then pass TRUE to column8 in data2.
But,
If it does not match then add the value of FFX (which is present in (column TaxAmount) in data2) to MRC(data2) and multiply the result (i.e FFX+MRC) with the value of TAX_RATE (data1). (by doing this the result should match to column6 (Percent) of data2) and as it is matching now so we should pass True to column8 in data2 or else False

Data1

wade.txt (1.8 KB)

Data2

test1.txt (1.5 KB)

Can anyone help please

Cross Reference / Duplicate of:

Hey @dipon1112000

You can follow these steps,

→ Use the “Read Range” activity to read the data from Data1 and Data2 Excel files into two separate DataTable variables.

→ Use the “Add Data Column” activity to add a new column named “Column7” to the Data2 DataTable.

→ *Use a “For Each Row” activity to loop through each row in the Data2 DataTable.

→ Inside the loop, use a “For Each Row” activity to iterate through each row in the Data1 DataTable.

→ Use an “If” activity to check if the value in the “TAX_TYPE” column of Data1 matches the value in the “TAX” column of Data2.

→ If there is a match, multiply the value in the “MRC” column of Data2 with the value in the “TAX_RATE” column of Data1.

→ Store the result in a variable.

→ Assign the value of the variable to the “Column7” column of the current row in Data2

Thanks,
HAPPY AUTOMATION!!

Thank you so much for helping, can you provide a work flow for the “if activity”

@dipon1112000 It’s been pretty much clarified by @Archana_Gulli. Please try to develop yourself and let community know if you face any issue.

Happy automation!

Regards,
Ashok :slight_smile:

Thank you for helping I have tried and I am able to achieve the Frist and second condition

But I am stuck on how to handle third condition

@dipon1112000

->Add another “If” activity:

Condition → row(“Column7”).ToString = row(“Percent”).ToString

Then, use an Assign activity: row(“Column8”) = “True”

->Use one more Assign activity outside the if activity and declare a variable called finalresult: Assign
->finalresult = (CDbl(row(“MRC”)) + CDbl(row(“TaxAmount”))) * CDbl(row(“TAX_RATE”))

->Add an If activity below assign activity:
Condition → finalresult.ToString = row(“Percent”).ToString

If true then assign : row(“Column8”) = “True”

If false then assign : row(“Column8”) = “False”

->Save the workflow.

If you find this as helpful then mark this as a solution.

Thanks,
HAPPY AUTOMATION!!