Comparing two data tables and finding the missing items

Help Studio
, ,
1

I have two data tables in the excel each have two columns.
I want to compare both the data tables and find the missing item.
But the thing is both columns are interdependent on each other. So I have a problem in comparing both columns on table 1 with both columns of table2

2

Hi @Sonam_Nath ,

Could you provide us with some example data and it’s expected output data ?
You could provide the files itself or screenshots of it.

3

Hey!
Thanks for replying!
I have these kind of two data tables.
First data table is this one
image
It’s location to location data.
Second data table is this one:
image
I want to match first data table with the second one and check if it’s missing any location.

4

@Sonam_Nath , Could you also provide the Expected Output for these input data ?

It would be more clear as to what logic needs to be applied.

5

We have total 25 cities so we need check with data table1 to see if there any missing location in data table1

6

@Sonam_Nath , Could you follow the Below Steps and check if it provides you the correct result.

  1. Consider DT1 and DT2 are the two datatables. Below query is used to fetch all values from two columns as an array.
    Use the below in an Assign Activity.
firstList = DT1.AsEnumerable.SelectMany(Function(x){x("Dispatch From").ToString,x("Delivery To").ToString}).Distinct.ToArray

Here, firstList is a variable of type Array of String.

Similarly, we can fetch the items from the 2nd Datatable as well.

secondList = DT2.AsEnumerable.SelectMany(Function(x){x("Dispatch From").ToString,x("Delivery To").ToString}).Distinct.ToArray

Here, secondList is a variable of type Array of String.

  1. Next we find the missing items in both the arrays like below :
missingItems = firstList.Except(secondList).Concat(secondList.Except(firstList)).ToArray

Here, missingItems is a variable of type Array of String.

Let us know if this doesn’t work or gives out an error.