Update columns of excel based on condition and compare colums from 2 sheets

Help
, , ,
1

Hi Everyone - Please help me with below scenario.

I have Data given in sheet “input” where col C has value YES and NO.
I have to update the value of col C as per below requirement:
→ If cell value is YES then update it to “Male”
→ If cell value is NO then update it to “Female”

After upadteing the datatable I have to write it to another sheet “output”.

2nd requirement is:
I have to compare col C from sheet “output” and sheet “actual” and if matches I have write the datatable in sheet “result” with col C as “MATHED” and “NOT MATCHED”.

I have attached the sheet for reference. If possible please share the .xaml file.

Excel column update and comparison.xlsx (10.7 KB)

2

Hi @navincemk

You can use the below approach for requirement 1

  • use a read range activity to read the data into an datatable
  • use a for each row activity to loop through the datatable
  • inside the loop, use an if condition to check whether the col C has the value yes or no

if row(“Gender”).ToString.Equals(“yes”)

  • based on the condition, update the column value within the two sections of the If condition using assign activity…

    if row(“Gender”).ToString.Equals(“yes”)
    Then
    row(“Gender”) = “Male”
    Else
    row(“Gender”) = “Female”

Now, write the datatable back into the excel file

For the second requirement

  • read both sheets into two datatables

  • Use Join datatable to join the datatables with the join type Left Join

    • Join using the name and also the gender

  • Now, use another for each row activity to loop through the joined dattable

  • Within the loop, use an if condition to check whether the columns from the second datatable is null or not. Why check this is, if the values from the second DT return as null, that means that record has not matched.

  • Now based on that, use another assign activity to update the data as matched or not.

  • Once done, write the data to the excel using write range.

3

Main.xaml (9.5 KB)

For the first scenario when I am using assign activity then it is giving compiler error. I have attached .xaml file for reference. Could you please provide teh solution in my .xaml file.

For the 2nd scenario I did not understand the solution properly. Can you please explain more or show in .xaml file.

Thanks. Waiting for your reply

4

Any help?

5

@navincemk

  1. Filter For Column C=“YES” add a computed column Gender=“Male”
    Filter For Column C=“NO” add a computed column Gender=“Female” and do a union.
  2. Choose a Unique key, and join the tables, the records that have joined are matched , rest are unmatched.