Comparing two excel columns and updating corresponding values

Hi everyone,

Can anyone please suggest me how to create a sequence to compare two columns from two excel sheets of same excel file and to get corresponding column value
For ex : I have two sheets with the column names as student Reg no and student Rank

  1. In first sheet i have only student Reg no and i need to update student rank by comparing student reg no in both the sheets. if the reg no is matching then i need to update corresponding Stud Rank else i should leave it has empty…
    Can anyone please suggest the ideas to complete this…

Happy Automation ,


1 Like

Hello @praveenchintu374

You can refer to the below post.

(From a In SourceDT.AsEnumerable Join b In SourceDT.AsEnumerable On a(“Column_Name”).ToString Equals b(“Column_Name”).tostring Select a).CopyToDataTable.Select().CopyToDataTable().DefaultView.toTable(False,”Name”)

Then loop through that list and update the cell value.

HI @Rahul_Unnikrishnan
I am able to get the values from two sheets but i am not able to update it on sheet1…when i try to update using write range …restriction of conversion from string to datatable

@praveenchintu374 can you share the screenshot of the flow andthe error which you are getting

Join datatable can be used.
join.xaml (9.4 KB)

I have developed same sequence as above script but my requirement is i need compare Stud Reg No in two excel sheets if its matching i need to take the corresponding rank of particular matched student and i need to insert in first sheet
RegNo Rank
112 5
115 2
1145 1
RegNo Rank

I need to compare reg no from sheet1 to sheet2 and i need enter the rank in sheet if both reg no are matching



I think then lookup datatable will work for you.

1)You can read the second sheet. Sheet2
2) use a for each row in datatable acitvity
3)use vlookup activity. Value to search will be the RegNo from the 2nd datatable ( CurrentRow(0).ToString)
4)Range you need to select the sheet1
5) Output you will get as RowIndex. You can use that row index .
6)Use Write cell activity (use the index from step 5) and value ( CurrentRow(1).ToString)

Hi @praveenchintu374
So for your scenario basically which is comparison and updation
Look up datatable would work

Kindly refer the below example hope this helps


The Dt3 can be written to sheet1 .
join.xaml (9.7 KB)

How do i use excel sheets to this sequence

Instead of build data table , you can use read range for dt1 and dt2.

Thanks @Rahul_Unnikrishnan @nikhil.girish @Priyanka_Ramesh

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