Check if specific value form dt1 exist in dt2


I have two DataTables:

In both of them is a column “Order Number”
I would like to take Each “Order Number” from dt1 and check if it exist in dt2

If exist-> take next “Order Numer”
If not exist → write comment in dt1 (Column “Comment’)” This order does not exist in dt2

How to do it?

Hello, @aleksandra.plichta5

You can use For Each Row in Datatable in Dt1 and put into if condition to search Order Number in dt 2 and use this expression:

dt2.asEnumerable.Where(Function (x) x.item(“Order Number”).ToString.ToUpper.Trim.Equals(curretRow(“Order Number”).ToString.ToUpper.Trim)).Count = 0

Basically, get Order Number and lookup in dt2. If Order Number not found in any row in dt2, Count return = 0.

Then in True branch, put assign activity to currentRow.item(“Comment”) = “This order does not exist in dt2”


FYI, another approach: using LINQ with Invoke code.

dt1.AsEnumerable.Where(Function(r) dt2.AsEnumerable.All(Function(r2) r2("Order Number").ToString<>r("Order Number").ToString)).ToList().ForEach(
    r("Comment")="This order does not exist in dt2"
End Sub
) (2.9 KB)


Let us mention this recommendation as well:

So we rewrite to any with predicate
…Any(Function (x) …)
…Where(Function (x)…).Any()

In case of another optimization is needed also have a look here:

we took benefits from lists/dictionary keys

1 Like

Very thanks for advice.

I took notice and I will sharing in another oportunities