For Each / Lookup / Data Table

Hi all.

Here’s the scenario.

I want to read the recipients list file using customer number. Then it locates and copies all associated data from the usage file, then write is as a new file (xls).

I can’t figure out how to locate and copy.

Appreciate all of your help.

sample_recipient_list2.xlsx (14.9 KB)
sample_usage_data2.xlsx (65.2 KB)


  • Read the both excel use read range activity and save it in datatable variable
  • Assign the below code to new datatable varaiable it will give the macthed data

TableA.AsEnumerable().Where(Function(ra) Not TableB.AsEnumerable().Any(Function(rb) rb.Field(Of String)(“Customer Number”) = ra.Field(Of String)(“Customer Number”))).CopyToDataTable()

  • Then save as excel using Excel scope activity
1 Like

hi @rojem

As far I can tell you are looking to find unique list from the Usage_data2 to Recipent_list2…
Is that correct?

1 Like

Hi @Shubham_Varshney,

It’s actually the other way around. Using the account number from the recipient list, I will look for all associated data inside usage_data, something like filtering it, then copy and paste to a new file.

yeah, it’s possible…
If you are comfortable with LINQ go with

Else do a filter activity, and find the unique data from the original sheet and find the number and rest info as per requirement!!!


I think this may help you to begin with: