Excel Vlookup: Copy and paste the matching rows

Help Studio
, ,
1

Hi all, I have 2 excel sheets (example below), I want to loop for each row to find are there any rows that company name in sheet 1 match the company name in sheet 2. For the matching rows, I need to copy the whole row and paste to sheet 3.
image

image
image1468×450 34.1 KB

Is vlookup activity the easiest way to make it? If yes, how should I do?

2
  1. Read the sheet1 & Sheet2 and store in dtsheet1 & dtSheet2
  2. use below LINQ expression in Assign Activity for result datatable (dtresult),
    The below LINQ can fetch the required result
    LINQ:
    dtsheet2.AsEnumerable().Where(Function(d2) dtsheet1.AsEnumerable().Where(function(d1) d1(“Company_Name”).ToString.Tolower.Trim.Equals(d2(“Company_Name”).ToString.Tolower.Trim)).Any).CopyToDataTable
  3. Write the dtresult datatable to the Sheet3 in excel.
3

oh so I do not need excel vlookup activity to run the tasks?

4

Thats not required. you can use LINQ to compare 2 datatable /Sheets

5

Thank you for your prompt reply :slight_smile:
image
I face this error when I’m trying to use the above linq, how can I fix it?

6

Error:
Excel Process Scope: The source contains no DataRows.
I fix the above error by changing string to datatable of dtresult but it still cannot run properly and the error shows again :frowning:

7

Can you share the error message

8

Here you go,

image
image1358×66 16 KB

and here is my linq:

9

image
image1367×140 25 KB

image

10

There is no common value in 2 columns you compare … Please check if the values n the columns