Compare two Excel Sheets using Linq

Hi,
I have two excel sheets(Test1) and (Test2) “attached in Zip”. Test2 contains list of strings which i want to check in Test1 excel file. If that string matches with any value in Test1 file then write that row in new excel file.
For example Test2 list has “inc” and Test1 has “GLOBAL, INC.” so the string matches and write that full row to new excel sheet or file.
Attached is the sample process and excel files.
Sequence1.xaml (5.5 KB)
excel files.zip (101.5 KB)

@zeshanm9
With a Base patterned linq give a try on Following
Assumed the result should have the Same structure as dt1

Use an assign activity
Left Side: yourresultdatatablevar
Right Side:

(From d in dt1.aseunumerable
Where dt2.Any(function ( r ) d(yourcolnameorindex). toString.contains(r(yourcolnameorindex).tostring))
Select d). CopyToDataTable

Hi @ppr,
Thank you for your reply. Now I am getting this error.

@zeshanm9
Sorry aß im writing from Cellphone some typos occurs quickly

Please Respekt the Line breaks on
Where, select

Error can be fixes with dt2.asenumerable.any…

sorry still i am not able to fix it. (From d In westlaw.AsEnumerable Where master.Any(Function ( r ) d(0). toString.contains(r(0).tostring)) Select d). CopyToDataTable

@zeshanm9
Pay Attention on master.Any
And Change to
master.asenumerable.Any

Ok it works but it is not writing thee result which i want. it is writing those strings which is not matched.
Also the format of the document is changed.

@zeshanm9
Here IT IS imprzant that you giving US better Feedback. From the distance WE cannot Deals with hiddens or unknown.

So Lets do one by one

Please Share some minimal Screenshots from Input 1and 2, the current Output, a description of expected Output

Thanks

i already attached the files for input 1 and input 2. Screenshot is here:

OK perfect i will have a Look on IT once i am Back on my Laptop.

@ppr thank you please let me know

Hi @Jacqui_M, any suggestion ?

Hey @zeshanm9

Yeah I went down a tangent of thinking a join query would work until I read that it’s more of a fuzzy search.

I do wonder though, if a for loop and filter datatable wouldn’t do the trick. I think the filter datatable might do the trick, as suggested here:

I’ll keep looking and playing around to see if I can also find a more elegant solution in the form of a linq query. I thought that ppr’s solution would’ve worked.

@Jacqui_M but it is not working . i am getting the wrong results.

Which solution did you get working? Maybe we can invert the results

i meem @ppr solution
in assign : (From d In westlaw.AsEnumerable Where master.AsEnumerable.Any(Function ® d(0). toString.contains(r(0).tostring)) Select d).CopyToDataTable

1 Like