Compare two columns from different sheets

Help StudioX
, , ,
1

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?

example:
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

2

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

Regards,

Nived N
Happy Automation

1 Like
3

@NIVED_NAMBIAR what is r1 and r2 in your code?

1 Like
4

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

5

can u pls share one xaml for it for learning?

6

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

(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