Compare Two excel and paste the 2nd excel matched data in 3rd excel

Help Community
1

Hi Team,

I have 2 excel files-

1st excel file contains 5 column ( Name , Email , Phone No. , City , country)

2nd excel file contains 3 column ( Name , City , Company)

So, i want to compare the 1st excel Name and city value with 2nd excel Name and city.

If the value is matched then i want to paste the second excel matched data ( Name , City , Company) into the 3rd excel file

2

@Shivam_Rana

Welcome to the community

Please use join datatable activity after reading both the files data into datatable. Join to be done on Name and city(inner join)

then use filter datatable to have only columns needed that are second file related columns (Name,City,Company) then paste it into the excel using write range activity

cheers

1 Like
3

@Shivam_Rana
Follow these simple steps.

  1. Read First File by read Range and Store in DT_A
  2. Read Second File by read Range and Store in DT_B.
  3. Make new Datatable DT_C.
  4. Use assign activity and left side give DT_C and Right side DT_A.Clone
  5. use one more assign activity and place DT_C.
    Right Side

(From a In DT_A Join b In DT_B On a(“Name”).ToString.Trim Equals b(“Name”).ToString.Trim and a(“City”).ToString.Trim Equals b(“City”).ToString.TrimSelect DT_C.Rows.Add({b(“Name”),b(“City”),b(“Company”)})).CopyToDataTable

2 Likes
4

its working @Anil_G

one more question let suppose 1st file Name and City value is Raj and Delhi

and 2nd file Name and city value is lh_Raj and city Aa_Delhi.

how can i implemented this logic in our already created logic.

5

@Shivam_Rana

If you want to consider these as matched…then you cannot use join activity…instead need to use join via linq

cheers

6

if possible can you please share the linq query with me to achieve this ?

Thanks

7

@Shivam_Rana

Please check this

dt2.AsEnumerable.Where(function(x) dt1.AsEnumerable.Where(function(y) y("Name").ToString.Contains(x("Name").ToString) AndAlso y("City").ToString.Contains(x("City").ToString)).Count>0).CopyToDataTable

This compared if dt2 has Name and dt1 has LH_Name …if it is other way rounf then interchange x and y in the contains statement

And gets the dt2 records which are matched

cheers

1 Like
8

IMG20230608130048
IMG202306081300481920×1440 337 KB

IMG20230608130058
IMG202306081300581920×2560 715 KB

i have implemented your logic but it is showing me the error.
Please help

9

@Shivam_Rana

Please paste everything in a single line

cheers

10

it worked :grin:

you are great @Anil_G :smiley:

cheers

1 Like
11

@Shivam_Rana

Happy Automation :slight_smile:

Cheers

12

Done :smiley::ok_hand:t2:

Thanks

1 Like
13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.