VLOOKUP in 2 data tables

[ppr (Peter Preuss)]

As an alternate, we can do the following: HashSet LookUp Approach

Vars:

Flow:

myLookUpHashSet =
new HashSet(Of String) (dt2.AsEnumerable.Select(Function (x) x("Column1").toString.Trim))
dt2.AsEnumerable.Select(Function (x) x("Column1").toString.Trim).ToHashSet()

row(“isMatch”) =
If(myLookUpHashSet.Contains(row("Column1").toString.Trim), row("Column1"),"NA")

Traces:

After update:

We evolved from LKDictionary to HashSet as we avoided the unneeded values from the keyValuePairs

LINQ query running for long hours

Let us introduce the next changes [grafik] we are creating lists with the concatenated strings from dtold, dtnew and creating a dictionary of only the items common in both lists dtNewSet.asEnumerable.Select(Function (x) String.Join(“#”, arrColSet.Select(Function (k) x(k).toString.ToLower.Trim))).Distinct().toList dtOldSet.asEnumerable.Select(Function (x) String.Join(“#”, arrColSet.Select(Function (k) x(k).toString.ToLower.Trim))).Distinct().toList ListDT1.Intersect(ListDT2).ToDictionary(Fun…