Compare 2 excel sheets using uipath

[alamelu.b]

I want to compare two columns of two different Excel sheets and return the corresponding 3rd column value of 2nd excel sheet infront of corresponding matched rows of 1st excel sheet.

Suppose Excel 1 contains follow value

Sl.No   Count
-------------------------------
105       1  
106       2  
107       3  
108       4  
109       5  
110       6  

Excel 2 contain

Sl.No   Count   Grade
-----------------------
108       4       A  
109       5       B  
110       6       C  
111       1       D  

Expected result

Sl.No   Count   Grade
-------------------------
105       1  
106       2 
107       3 
108       4       A  
109       5       B  
110       6       C  

Please suggest, what function should I use. by using uipath?

[Sanjit_Pal]

Hey @alamelu.b,

Have a look on this thread.

How to compare two excel sheet & update the matched value in sheet 2

Hi all, I have 2 worksheets, In sheet 1 it holds ID & Amount and in sheet 2 it has only ID. I have to compare both sheet & If Id matched I want to get the amount and write in sheet 2, example, Worksheet A ID Amount 1111 $123 2222 $678 3333 $980 4444 $650 Worksheet B ID Amount 1111 $123 5555 3333 $980 0444 Thanks in advance

Thanks,
Sanjit

[Gokul001 (Gokul Balaji)]

Hi @alamelu.b

Welcome to Uipath Community

You can try with Lookup DataTable activity

Check out the Documentation

Lookup Data Table

Here is the Workflow

ExcelCompare.xaml (17.0 KB)

Output

Regards
Gokul

[alamelu.b]

thank you for your respnse but i used linq only not for each

[raja.arslankhan (Raja Muhammad Arslan khan)]

Hi @alamelu.b,
DT1.AsEnumerable().Where(Function(row) DT2.AsEnumerable().Where(function(r) r(“ColName”).ToString.Equals(row(“ColName”).ToString).Any).ToArray

please try This One

[rohith.prabhu]

Hello @alamelu.b ,

You can use the below Linq to achieve this :

Use assign activity and assign
outDT = TestDt2.Clone()

Assign :

outDT = (From R1 In TestDt1.AsEnumerable
From R2 In TestDt2.AsEnumerable
Where ((R1.Item(“SI.No”).ToString.Trim.Equals(R2.Item(“SI.No”).ToString.Trim) AndAlso (R1.Item(“Count”).ToString.Trim.Equals(R2.Item(“Count”).ToString.Trim))
Select R = outDT.Rows.Add((R1.ItemArray.Append(R2.Item(“Grade”).ToString).ToArray))).CopyToDataTable()

Please let us know if you face any challenges in implementation.

Regards,
Rohith

[alamelu.b]

thank you for your response

[ppr (Peter Preuss)]

@alamelu.b
Your case can be matched to different approaches

Left Join (with the assumption on no duplicated S1.No values in dt 1

Assign activity
dtResult = Dt2.Clone

Assign activity:
dtResult =

(From d1 In dt1.AsEnumerable()
Group Join d2 In dt2.AsEnumerable()
On d1("S1.No").toString.Trim Equals  d2("S1.No").toString.Trim Into gj= Group
From g In gj.DefaultIfEmpty
Let grd= If(isNothing(g), Nothing, g("Grade"))
Let ra = d1.ItemArray.Append(grd).toArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

Match-Lookup
Assign activity
dtResult = Dt2.Clone

Assign activity:
dtResult =

(From d1 in dt1.AsEnumerable()
Let sno = d1("S1.No").toString.Trim
Let mtc= dt2.AsEnumerable.Where(Function (d2) d2("S1.No").toString.Trim.Equals(sno)).toList
Let grd = mtc.Select(Function (g) g("Grade")).DefaultIfEmpty(nothing).First()
Let ra = d1.ItemArray.Append(grd).toArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

@kumar.varun2 thanks for review

[alamelu.b]

hi, i got ")"end of exception error

[rohith.prabhu]

Hello,

Probably a ‘)’ is missing. I have corrected expression, please use the below :

(From R1 In TestDt.AsEnumerable
From R2 In TestDt2.AsEnumerable
Where ((R1.Item(“SI.No”).ToString.Trim.Equals(R2.Item(“SI.No”).ToString.Trim)) AndAlso R1.Item(“Count”).ToString.Trim.Equals(R2.Item(“Count”).ToString.Trim))
Select R = NewDt.Rows.Add((R1.ItemArray.Append(R2.Item(“Grade”).ToString).ToArray))).CopyToDataTable()