Compare three columns in two excel and update the main excel

Hi Everyone

My requirement is
1.I have two excel output and Main
2.need to compare three columns from two excels eg: org name , reg no. and email id
3 if they are same update the main excel with a column value in output excel

I used used lookup to compare reg no. in two excel and update but now more conditions like org name and email id along with reg no. has been added to requirement .

Thanks in advance

We can do the same for these two new columns as well

Are we facing any challenge in that

Anyhow there are two options
Where one is to use lookup and another one is linq query

as you have already implemented lookup datatable it’s the same for any new columns

Cheers @kavya.s16

@Palaniyappan thanks for the reply
Yeah i used lookup for one columns , but i am confused on how to use it for multipe columns , could u please help on that .

It’s the same as first one but with two different lookup datatable activity with appropriate properties been set

No worries

Can we have a screenshot on how first one was done


Sorry will not be able to provide screenshot as its client data

but yeah i can explain
I used read range to read output excel - dt2 (datatable variable)
inside excel scope used read range to read main excel - dt1
cloned it - rdt

then for each datatable of dt1 - another variable row_index_1
used look up datatable for dt2 with column name - got row index_2

if row index_2 <>-1

updated cloned datatable rdt accordinly

eg :rdt.rows(row index_1).item(columnname) = dt2.rows(row index_2).item(column name)

hope u r able to understand

If your comparing all the columns of DT1 and to the columns of DT2 use this
dt1.AsEnumerable.Intersect(dt2.AsEnumerable, System.Data.DataRowComparer.Default).CopyToDataTable()

have a check on a join datatable approach

using a LINQ or Join Datatable Activity for it

unfortunately it is not describing which value is to use for the update

In other cases we can also use a LINQ for checking / retrieving the match in dt2.

The best would be you can share some sample data along with the expected output description. based on this we will workout a solution suggestion more individually to your case.

1 Like

Can you try this

(From t1 In dt1.AsEnumerable Join t2 In dt2.AsEnumerable On t1(“reg”).ToString.Trim Equals t2(“Emp Id”).ToString.Trim
Where t1(“orgname”).ToString.Trim.Equals(t1(“orgname”).ToString.Trim) And
Select t1).CopyToDataTable