Get item different from 2 datatable

Hi Everyone.

I have 2 datatables in the two sheets as attached file.

I want to find the Storage Code if any item not exist in both of two sheets.

As attached file… the result will received are : “2”,“4”,“5”

Thanks in advance!
Book1.xlsx (9.6 KB)

Try the following:
1/ Load “Sheet1” into datatable dt1
2/ Load “Sheet2” into datatable dt2
3/ Use following expression in assign, “result” is array(of Int32)

result = (From a In dt2.AsEnumerable
Group Join b In dt1.AsEnumerable
On a("Item") Equals b("Item") And a("Code") Equals b("Code")
Into ab = Group
From g In ab.DefaultIfEmpty()
Where Not CStr(a("Quantity")).Equals(CStr(If(isNothing(g), "", g("Quantity"))))
Select CInt(a("Storage"))
).ToArray

Cheers

1 Like

Hi Bro.

I want to received result and write into one column on another sheet “Sheet3” on the same file.

And i want to check if any value from “item”, “Code”, “Quantity” is different between two sheets… it will return the Storage value.

As your solution… the result as array ( int32 ). How to change it?

Thanks you very much!

I do not know how to read this.

From your description I got impression that “Item” and “Code” are keys based on which two tables should be linked. Then records with different “Quantity” should be selected and corresponding “Storage” returned. This is what the above expression does.

If the point is just get the result as datatable instead array(int32) you could make it like this

dt3 = (From a In dt2.AsEnumerable
Group Join b In dt1.AsEnumerable
On a("Item") Equals b("Item") And a("Code") Equals b("Code")
Into ab = Group
From g In ab.DefaultIfEmpty()
Where Not CStr(a("Quantity")).Equals(CStr(If(isNothing(g), "", g("Quantity"))))
Select dt3.LoadDataRow(New Object() { CInt(a("Storage"))}, False)
).CopyToDataTable

Cheers

1 Like

First you can read the excel data by read range activity
now you can use the below code and it can be a stored in a data table
next you can create data table by using build data table activity
inside for each row activity add ‘add data row’ you can give the arrayrow property to {row(“Storage”)} and give the data table name (builded dt)
lastly you can use write range activity to update storage column to excel.

1 Like

DT1.AsEnumerable().Except(DT2.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

2 Likes

Hi Bro.

I have encountered error as below picture

Hi,

In the second read range activity give ‘sheet2’ to the sheet property and also you can change the code like this

DT2.AsEnumerable().Except(DT1.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

1 Like

Hi Bro.

Your mean is change this code “DTx.AsEnumerable().Except(DTx.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable” into the red highlight position as picture?

Hi,
You will check the code in the below attached file
Main.xaml (11.4 KB)

1 Like

Thanks you very much bro…

I will check and inform to you result soon.

Hi @Mr.H ,

It’s ok
but i am not bro

call me as @navyavara or sister

1 Like

Hi @Mr.H ,
is this solution please tick the mark

1 Like

Sorry.

Thanks you very much :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.