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
- 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 ,
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.
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
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)
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.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.