How to compare columns of two diiferent excel

I have to compare column 2 of excel 1 and with column 2 of excel 2.
If they match I have to extract the the cell value from colum1 of excel one corresponding to the match value of column 2 of excel 1

Hi
we can use lookup datatable activity buddy
For more info on this

Cheers @Karan28

2 Likes

but here we are comparing two differenr excel, then?

Thats is the main purpose of that activity
we get two different datatable from two excel
which can be checked with lookup datatable with one datatable as input datatable and another as target datatable
Cheers @Karan28

thankyou very much , let me check and get back to you

1 Like

Fine
Kindly try and let know for any queries or clarification buddy
Cheers @Karan28

Hi @Karan28,

If the data in your excel files are not so big, you can use nested for each

Yes ,I saw the video but my question is slightly different.
First I need to compare the Column 2 of excel 2 with the column 1 of excel 1.
If any of the values of excel 1 column matches with column of excel 2 then after that I will extract the respective Column 1 values of excel 1
you get my ques?

1 Like

No worries buddy @Karan28
Fine in that case kindly follow the below steps that could surely solve your issue
‚Äďhope you have two datatables named outdt1 and outdt2 of type datatable
‚Äďuse a for each row loop and pass the variable outdt1 and change the variable name from row to row1
‚Äďinside this use another for each row loop and pass the variable as outdt2 and change the variable name in for each row loop from row to row2
‚Äďinside this inner for each row loop use a if condition like this
row1(‚ÄúColumnname‚ÄĚ).ToString.Equals(row2(‚ÄúColumnname‚ÄĚ).ToString)
if this condition gets passed it means there is a match and will go to the THEN part of if condition where we can get the value of column 1 in excel 1 like this
out_value = row1(‚Äúcolumnname‚ÄĚ).ToString
where out_value is a variable of type string
‚Äďmake sure that a break activity is used next to this assign activity in the THEN part of if condition to avoid getting matched again once the match is found, so that the inner for each row loop will be broken and will go back to the next row in the first for each row loop

Thats all buddy you are done
Kindly try this and let know for queries or clarification
Cheers @Karan28

1 Like

@Palaniyappan did u see my reply or not before posting this reply…:sob:

row1(‚ÄúColumnname‚ÄĚ).ToString.Equals(row2(‚ÄúColumnname‚ÄĚ).ToString)
this condition check the column name but here I need to compare the value of two columns in two excels and if they match I need to extract the respective value of column 1 from excel 1

Not like that @Karan28

row1(‚ÄúColumnname‚ÄĚ) is not column name. it is a column value only, on the first iteration
row1(‚ÄúColumnname‚ÄĚ) will return the first value in the column.

example:

age

row1(‚ÄúAge‚ÄĚ) will return the 22 for first iteration.

Okay Karun , thankyou

No Buddy
actually row1(‚ÄúColumnname‚ÄĚ).ToString will fetch the value in the current row in the loop under the columnname mentioned within double quotes, it fetches that cell value not the columnname
similarly does the row2(‚ÄúColumnname‚ÄĚ).ToString
here the EQUALS method will check whether they are equal or not
so the values are compared buddy not the columnname

Kindly try and let know
Cheers @Karan28

Thankyou so much , thankyou for your time it worked.

1 Like

Fantastic
Cheers @Karan28

1 Like

now I can use the Vlookup datatabkle activity right after this ?

If this condition is not going to be used, yes if you want you can use vlookup of course
but as you have used to condition out here it gets looked up and there is no need to using vlookup buddy
Cheers @Karan28

1 Like

I used the v lookup data table activity and it worked thankyou

1 Like

Fine
Cheers @Karan28