Unable to compare values in 2 different excel sheets

[usertest114]

There are two sheets in excel, Sheet1 has A~Z randomly entered in cells A1:A26 and Sheet2 has A,B,C entered in cells A1:A3. Check if Sheet1 has the values of Sheet2(A,B,C) and if so, put a check mark in the adjacent cell (eg. if A is entered in A1 on Sheet1, I would like to add check mark on cell B1 on Sheet1). I want to enter a check mark in the adjacent cell (B). I tried to implement it with for each loop and if statement but it didn’t work. (CurrentRow.item(“”).ToString.Contains did not work in this case.)

[Gokul_Jayakumar (Gokul Jai)]

Hello, @usertest114 kindly share your sample input and expected output, it will help us to provide better solutions for you.

[supermanPunch (Arpan)]

@usertest114

I tried to implement it with for each loop and if statement but it didn’t work. (CurrentRow.item(“”).ToString.Contains did not work in this case.)

Hi @usertest114 ,

Let us know your method of approach that you are using to detect the values present or not in another sheet and also maybe screenshots of your implementation.

Also make sure to perform/use a Write Range Activity back to the Sheet because the sheet needs to be updated with the updated datatable values.

[usertest114]

Here is my sample input. I’m not sure how to write condition/cell. In write cell, I would like to put a check mark in the cell next to the alphabet match in sheet2.
A1 on both excel sheets is labeled alphabet.

[supermanPunch (Arpan)]

@usertest114 ,

As you are using Excel Modern activities with that approach you wouldn’t need to use Write Cell Activity but you would need to use two For Each Excel Row Activities to loop through the contents in both Sheets, in your Screenshot you are only accessing the contents of Sheet1.

Next you could use the References for each row of Sheet1 (CurrentRow1) and Sheet2 (CurrentRow2) for comparison of values and then update the check column accordingly using an Assign Activity.

Do note that I have used the Sheet2 as the Outer For Each Excel Row Sheet. Also Use Of Excel Process Scope needs to be added as the Outermost container when using the Modern Excel Activities.

An Alternate approach would be to use Datatables and update datatables and write back the updated datatable value back to the excel sheet.

Let us know if you are still facing difficulties in performing this.

[usertest114]

Thanks a lot! I could implement without issues.