Excel Automation Issue :Please help

Help Activities
, ,
1

Hello everyone ,

I need to apply vlookup from one excel to another on
Excel 1 -Invoice Number Column to Excel 2 - Invoice Column and update the value of excel 1 status column value from excel 2 comment column.

Note- please provide solution that doesn’t include join data table activity and vlookup function using write cell.
I need linq query for this operation i need only excel 1 file columns because sometimes excel 1 columns will update.

2

@Luffy

Not sure why you dont want to do a join which is best for this case…

Anyways here is a linq that you can use in invoke code with passing dt1 as in/out and dt2 as in argument

dt1.AsEnumerable.ToList.ForEach(Sub(r) r("Status") = dt2.AsEnumerable.Where(function(x) x("Invoice").ToString.Equals(r("Invoice").ToString)).Select(function(x) x("Comment").ToString).FirstOrDefault)

Cheers

3

Hey @Luffy
you can try this:
dtExcel1.AsEnumerable().ToList().ForEach(Sub(row) row("Status") = If(dtExcel2.AsEnumerable().Any(Function(x) x("Invoice").ToString().Trim() = row("Invoice Number").ToString().Trim()), dtExcel2.AsEnumerable().FirstOrDefault(Function(x) x("Invoice").ToString().Trim() = row("Invoice Number").ToString().Trim())("Comment").ToString(), row("Status")))