Mapping Values from excel file using dictionaries

Hi all,

I’m facing issue with getting required output from excel file using some conditions.

I have excel data like this

I need to split each keyword in 2nd column and check particular keyword. if keyword existed i need to take column 1 value as output. If we found keyword multiple times. we need to take all mapped ones.

Input string - Banking
output - Banking, Financial Services & Insurance
but i’m getting Investment Bank also in output because it contains ‘Investment Banking’ in B column

I have developed code like this
read excel sheet - output - dt_CurrentIndustry
creating dictionary - marketDictCurrentIndustry

marketDictCurrentIndustry = dt_CurrentIndustry.AsEnumerable.ToDictionary(Function(x) x(“Industries in SalesForce (Current Industry)”).ToString, Function(y) String.Join(“,”, y.ItemArray()))

If(marketDictCurrentIndustry.Where(Function(x) x.Value.ToUpper.Contains(str_GetPriorIndustries.ToUpper.Trim)).Count > 0, string.join( “;”, marketDictCurrentIndustry.Where(Function(x) x.Value…ToUpper.Contains(str_GetPriorIndustries.ToUpper.Trim)).ToList.ConvertAll(function(x) x.key)) & “;” & IndustryType, IndustryType)

Assuming in the second column, the items are separated by a semi-colon + space, then we can split the second column to an array and search the array items.

No need to convert the DataTable to a Dictionary. You can try something like this, which will return an Array of Strings:
image

If you do .Contains() on a string, then you are searching the whole string. This is why “Investment Banking” gets included because it includes the string “Banking”. But if we do .Contains() on an Array, then it has to be an exact match of the Array item.

Hope that makes sense!

Thank you for your response.

Happy to help! If this resolved your issue, please consider marking the response as the solution!

I’m getting this error late binding is not possible…

Make sure you are using the DataTable not the Dictionary. And check syntax throughout. If it helps, share a screenshot.