Filter data from data table 1 to data table 2

Hi, i have 2 excel.

I need to get “Person Number” from Excel 1 and check if the person number has a duplicate or existed in Excel 2.
if it has a duplicate Person number in excel 2 i need to filter the latest “Hire Date”

Output
Person Number | Hire Date
303030 | 9/17/2021

Thank you in advance.

Hi,

Hope the following sample helps you.

dtResult = dt2.AsEnumerable.Where(Function(r) dt1.AsEnumerable.Any(Function(r2) r2("Person Number").ToString=r("Person Number").ToString) AndAlso dt2.AsEnumerable.Count(Function(r2) r2("Person Number").ToString=r("Person Number").ToString)>1).GroupBy(Function(r) r("Person Number").ToString).Select(Function(g) g.OrderBy(Function(r) DateTime.Parse(r("Hire Date").ToString)).Last).CopyToDataTable

Sample20210927-1.zip (4.8 KB)

Regards,

Hi @Yoichi it worked, but i think i should put it inside fore each row?
What if i have multiple Person Number duplicates.

becoz i have multiple filtering activity in 2 Excel file.

Hi,

Probably we don’t need to put it in ForEachRow. The above expression will output filtered datatable directly.

First, Read Range Excel1 as dt1, Read Range Excel2 as dt2, then use the above.

Regards,

Hi @Yoichi

Im having a hard time to understand the code. :frowning:
Can you tell me exactly
if
r2(“Person Number”) is for dt1?
r(“Person Number”) is for dt2?

i need to change the column name in dt2 because it had to uncheck(uncheck headers) so instead of “Person Number” it will give me “Column1”

hi @Yoichi I forgot to mention that the excel above Image is .CSV file.
does it affect on the code that you provided?

in_dtRoaster.AsEnumerable.Where(Function(r) in_dtInternalMobility.AsEnumerable.Any(Function(r2) r2(“Person Number”).ToString=r(“Column1”).ToString) AndAlso in_dtRoaster.AsEnumerable.Count(Function(r2) r2(“Person Number”).ToString=r(“Column1”).ToString)>1).GroupBy(Function(r) r(“Column1”).ToString).Select(Function(g) g.OrderBy(Function(r) DateTime.Parse(r(“Hire Date”).ToString)).Last).CopyToDataTable

image

Hi,

No. The exception of the above image means there is no column named “Person Number”. Do you read csv file with header? if so, can you check “Person Number” column in it?

Regards,

Hi @Yoichi

This is my CSV File.

image

This is My Excel File:

I dont know why it give exception like that.