Comapre two columns from two excel sheets

Help
1

Hi ,

I want to comapre two different values/columns from two excel sheets can any one help me out in this that which activity will be used and what will be fllow i am stucked in it please guide.

Regards.

2

Did you tried LinQ?

3

NO I didnt can you send me short workflow how to comapre two values from two excel sheets using linQ or any other activity if two values are equal then we will get a message box saying values are equal

4

Uipath has an old tutorial on how to do this exact thing: https://www.uipath.com/kb-articles/excel-automation-compare-rows-from-different-tables

5

thanks i will see and then will post again if i find any issue

6

i tried but its working for me can anybody tell how can i point to exact column and row which i want to compare i am using this [1][2] is this correct format 1 is for 1st row and 2 is for 2nd column.Kindly help

7

you can reference columns by name or by index. Note that index in uipath starts at 0 whereas the index in excel starts at 1.

You can reference rows by index only, as there is no such thing as row title

8

@Dave can you send me simple work flow i want to comapre two values in different data tables if values exist in both excels then it should show a message that values matched
plz help me out in this

9

Here is a quick workflow. It reads 2 excel files, then outputs matching rows by comparing a single column within each workbook.

ExcelCompare.zip (14.1 KB)

10

thanks @Dave i will let you know if i found any issue during its execution

11

thanks @Dave i just have few question the code you sent it matches column “ID” of both sheets?.correct me if i am wrong. no matter what ever the values in the rows it will just compare the same id and will return the row right?

and one other thing if i want to compare a specific value in two sheets then how should i write it

and last can you explain this piece

image
image718×35 2.6 KB

Regards.

12

the code you sent it matches column “ID” of both sheets?

That’s correct. You could also reference the column index instead. In this example, since ID is the first column in both excel sheets, you could instead say cint(Book2row.item(0)) = cint(Book1row.item(0))

no matter what ever the values in the rows it will just compare the same id and will return the row right?

Yes. The cint() portion is converting it to integer. You could also change it to string and compare the two strings, but it in general you want to compare the value in book1 to the value in book2. I would always recommend converting the value to a string or number, because it will sometimes not realize values are the same if you keep them as their plain “object” type.

and last can you explain this piece

This is just the output I did to show when matches were found. You would insert your own activities here. But it is just a concatenated string meant to show which row in book2 matches which row in book1. The piece Book2.Rows.IndexOf(Book2row) gives you the index of the book2 row you’re currently on in the book2 For Each loop. You then add +1 to the end of it because UiPath starts at index = 0 while Excel starts at index = 1. Since we are in a Write Line activity, we have to convert that index to a string. We then do the same thing again with Book1 to show which row we’re currently looking at in the book1 For Each loop

13

Thanks dave you are very helpfull one last thing if i want to campare a sting or an integer lets say i want to campare “dave” if it exists in both the sheets what should i write in if condition oly i need to comapre “dave”. do we still need to write book1row and book2row with column names? or condition will be different please write condition so that it can be helpfull for me and it message box it must show dave or value exists

Thanks

14

Do you want to every column for the value, or just a specific column, or multiple specific columns?

15

i just want to comapre a specific value in a specific column. can we comapre two values with out mentioning specifc columns?.lets say we donot know in which column the values is can we also find the values then?

16

@Dave what will be the condition if i want to campare a specific value can you please guide

17

I am on mobile and don’t have a computer in front of me, and will be gone for the next week so hopefully someone can jump in with more specifics if needed.

After the first ‘for each’ activity for book1, add an ‘if’ activity. The if condition should check Book1row.item(“ColumnName”).ToString = “Dave”. Leave the false portion of the if statement blank. In the true portion, put in the second ‘for each’ activity to search through book2. Add another ‘if’ activity within the book2 ‘for each’ activity. The if condition should check Book2row.item(“ColumnName”).ToString = “Dave” . Leave the false portion of the if statement blank. In the true portion add whatever code you want to process the matching transactions.

Sorry for the sloppy format, I hope it makes sense.

18

Thanks @Dave i will try and will let you know thanks alot

19

image
image786×440 7.96 KB

image

as you said i have used the same conditions please refer to the screen shots now in message box what should i use so that i may tell that dave found.i cannot use row of index in massage box as it will reutrn the matching row so what should i write

Thanks

21

Hi,

How to compare two columns values in two different sheets in excel and update the status whether they are equal or not in first sheet.please,help me .