Comparing two excel columns and updating corresponding values

Help Activities
, , ,
1

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 ,

Regards,
Praveen

1 Like
2

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.

3

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

4

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

5

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

6

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
Sheet2
RegNo Rank
112 5
115 2
1145 1
Sheet1
RegNo Rank
115
1145
1125
112

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

Regards,
Praveen

7

@praveenchintu374

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)

8

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

Thanks

9

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

10

How do i use excel sheets to this sequence

11

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

12

Thanks @Rahul_Unnikrishnan @nikhil.girish @Priyanka_Ramesh

13

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