Compare three columns in two excel and update the main excel

[kavya.s16]

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

[Palaniyappan (Palaniyappan P)]

@kavya.s16

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

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

[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 .

[Palaniyappan (Palaniyappan P)]

@kavya.s16

but i am confused on how to use it for multipe columns

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

@kavya.s16

[kavya.s16]

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

[KarthikBallary]

Hi
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()

[ppr (Peter Preuss)]

have a check on a join datatable approach

Story line of Join Data Table

Dear innovator’s, All we know that there is a Join Data table activity in UiPath.If we get any matching scenarios with unique value will approach join data table activity instead of filter/loop/array matching activity to find an exact match. Join Data Table: [image] Combines rows from two tables by using values common to each other, according to a Join rule, which is specified in the Join Type property. Sample Table: [image] INNER Join: The INNER JOIN keyword selects records that have m…

using a LINQ or Join Datatable Activity for it

@kavya.s16

if they are same update the main excel with a column value in output excel

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.

[KarthikBallary]

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
t1(“email”).ToString.Trim.Equals(t2(“email”).ToString.Trim)
Select t1).CopyToDataTable