Partial Lookup

Hi,
Need your help here to solve my query. Here is the simple and short example given, if I want to vlookup and partial vlookup from excel1 to excel2. How would I write in the IF condition.
Expected result: Charles Tee and Charles Elise T (exact match in vlookup) and Jane, Janet, Charles (partial match) as the First Name appear in both excels. If it is a long list like 400+ rows, I can’t use “contains”, so what should I write.

Excel 1:
image

Excel2:
image

Hi @zixin_wong

Please use the linq Function Below

(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1("Name").ToString.Trim Equals d2("Name").ToString.Trim
Select d2).toList

Refer the xaml below
SampleWorkFlow.xaml (14.1 KB)

Regards

1 Like

Hi @zixin_wong ,

Try below vlookup formula

=IF(OR((VLOOKUP(A2,Sheet2!A2:A3,1)=“Charles Tee”),(VLOOKUP(A2,Sheet2!A2:A3,1)=“Charles Elise T”)),TRUE,FALSE)

The only think is remaining here is to match partial case.
I am trying to figure out this.

1 Like

HI, Can you explain what is d1 and d2 here?
and why need to select d2.toList?

Hi @zixin_wong

D1 refers to the Read range value of Excel1

D2 refers to the read range value of Excel2

Comparing the values that are equal from D1 and D2 and collecting the values to List.

Regards

Hi Parvin,

Thanks for your explaination.
If i already put read range at the top and how should I put in the If condition?

Hi @zixin_wong

Can you share me your excel files if is possible?

Regards

Test_1.xlsx (9.6 KB)
Test_2.xlsx (9.6 KB)

We will stick to the example given above with excel 1 & 2. Thanks.

Hi @zixin_wong

Please refer to xaml that i have done with the test files that you are provided.

SampleWorkFlow (1).xaml (11.4 KB)

Hope this helps you!

Regards

Hi @pravin_calvin ,

Thanks for helping here but my expected result as per the screenshot. The result is partial match with the first name in excel 2 and result only show trim first name that match.
image

Hi @zixin_wong

@ppr @Yoichi will help in this case!

Regards

@zixin_wong
Flow:
grafik

Create a datatable for the report:
grafik

LINQ:

(From d In dt1.AsEnumerable
Let sp = d("Name").toString.Split({" "}, StringSplitOptions.RemoveEmptyEntries)(0)
Let chk = dt2.AsEnumerable.Any(Function (x) x("Name").toString.Split({" " }, StringSplitOptions.RemoveEmptyEntries)(0).Equals(sp))
Let m = If (chk, sp, "")
Let ra = New Object(){d("Name"), m}
Select dtReport.Rows.Add(ra)).CopyToDataTable

So it will split the names on space and will compare the first Split result (First name token)

Hi,

FYI, another solution:

dtResult = dt1.AsEnumerable.Select(Function(r) dtResult.Clone.LoadDataRow({r("Name"),if(dt2.AsEnumerable.GroupBy(Function(x)  System.Text.RegularExpressions.Regex.Match(x("Student Name").ToString,"^\w+\b").Value).Select(Function(g) g.Key).ToList().Contains(System.Text.RegularExpressions.Regex.Match(r("Name").ToString,"^\w+\b").Value), System.Text.RegularExpressions.Regex.Match(r("Name").ToString,"^\w+\b").Value ,"")},False)).CopyToDataTable()

Sample20210806-6.zip (17.1 KB)

Regards,

Hi @Yoichi,

Thanks for your help here.
Would like to know how should amend the code if i want the result to show in middle column (column F) and there is still another info at the backresult.xlsx (9.0 KB)

.

Hi,

First, modify schema of dtResult at BuildDataTable activity
Then, use following expression to create dtResult. (Modify arguments of LoadDataRow method)

dtResult = dt1.AsEnumerable.Select(Function(r) dtResult.Clone.LoadDataRow({r("Name"),r("Age"),r("Parent Name"),r("Phone"),r("Posscode"),if(dt2.AsEnumerable.GroupBy(Function(x)  System.Text.RegularExpressions.Regex.Match(x("Student Name").ToString,"^\w+").Value).Select(Function(g) g.Key).ToList().Contains(System.Text.RegularExpressions.Regex.Match(r("Name").ToString,"^\w+").Value)    , System.Text.RegularExpressions.Regex.Match(r("Name").ToString,"^\w+").Value ,""),r("Remarks")},False)).CopyToDataTable()

Sample20210806-6v2.zip (29.1 KB)

Hope this helps you.

Regards,

1 Like