Compare two columns from different sheets

I have two sheets in excel with col a and col b in first sheet and col c and col d in second sheet. col b and col c are primary key coloums…I need to compare col a value with col d and if difference is spotted then results needs to be written in third sheet containing col a,col d and col c(at which difference is spotted).Can anyone explain the logic to do this?

col a col b col c col d
abc 1 1 abc
bcd 2 2 zzz
ddd 3 3 qqq
ggg 4 4 ggg

results should be in third sheet as below:

bcd 2 zzz
ddd 3 qqq

Hi @rahul_kumar4
Try this
(From r1 in dt1.AsEnumerable()
From r2 in dt2.AsEnumerable()
Where r1(“col a”).ToString Equals r2(“col d”).ToString
dt3.Rows.Add(r1(“col a”),r2(“col d”),r2(“col c”))).CopyToDataTable

where the dt3 is the resultant datatable to which output results should be stored
with all columns name being defined

or another way

use join query


Nived N
Happy Automation

1 Like

@NIVED_NAMBIAR what is r1 and r2 in your code?

1 Like

It is row actually

dt1 - datatable variable after reading sheet 1

dt2- datatable variable after reading sheet 2

dt3- output datatable where written to third sheet

Hope you got the idea

can u pls share one xaml for it for learning?

similar to your case we can do with an inner join:

(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1(“Column2”).toString Equals d2(“Column3”).toString
Where Not d1(“Column1”).toString.Equals(d2(“Column4”).toString)
Let ra = New Object(){d1(“Column1”),d1(“Column2”),d2(“Column3”)}
Select dtResult.Rows.Add(ra)).CopyToDataTable

dtResult is an empty prepared datatable reflecting the column structure of the expected output and can be done e.g. with build datatable

find starter help here:
InnerJoin_1Col_JColLRCols.xaml (11.5 KB)

1 Like