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)
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
(From d in dt1.aseunumerable
Where dt2.Any(function ( r ) d(yourcolnameorindex). toString.contains(r(yourcolnameorindex).tostring))
Select d). CopyToDataTable
Thank you for your reply. Now I am getting this error.
Sorry aß im writing from Cellphone some typos occurs quickly
Please Respekt the Line breaks on
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
Pay Attention on master.Any
And Change to
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.
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
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 ?
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