How to match and mapping values in an excel

[ppr (Peter Preuss)]

it is all about requirment definition and sample data

When

@Kavya_S

Column2 : [POST_ADMIN, ACCESS_ADMIN, TINCOMP, upstock_ADMIN, UI_ADMIN, Comp_ADMIN]

contains ACCESS_ADMIN

and

@Kavya_S

InputRoleVar : [POST_ADMIN, TINCOMP, upstock_ADMIN, UI_ADMIN, Comp_ADMIN, ACCESS_ADMIN]

is the input then it looks like we want to search for roles from input in the mapping.

So we split the input roles on ,

(From d in dtMapping.asEnumerable
Where d("App").toString.Trim.Equals(yourAppKeyVar)
Let rls = InputRoleVar.Trim("[]".ToCharArray).Split(","c).Select(Function (x) x.Trim).ToArray
Let wrl = d("Role in website").toString.Trim(" []".ToCharArray).Split(","c).Select(Function (x) x.Trim).ToArray
Where rls.Intersect(wrl).Any()
Select s = d("Role in Record").toString.Trim).FirstOrDefault()

and also recommended to externalize to variables when it is possible

UPD1 - included [ ] trim

UPD2 - fixed based on update from:

How to match and mapping values in an excel part2

Support 1 from inputRoles is wrongly matching row 1, when doing a string Contains. Lets rewrite to the following: Assign activity strRoleInRecord = (From d in dtMapping.asEnumerable Where d("App").toString.Trim.Equals(yourAppKeyVar) Let rls = InputRoleVar.Trim("[]".ToCharArray).Split(","c).Select(Function (x) x.Trim).ToArray Let wrl = d("Role in website").toString.Trim(" []".ToCharArray).Split(","c).Select(Function (x) x.Trim).ToArray Where rls.Intersect(wrl).Any() Select s = d("Role in Reco…