Match 2 column in disorder from 2 different excel and foreach match, export in a third excel the entire matched row of the first excel


#1

Hi guys,

I’m stuck in my project and i can’t find a solution.
I explain myself, I try to read an excel file and a spreadsheet and when there is a match (the text of the both column of the excel and the spreadsheet are the same) to write the content (of the row +1 on the right of the Excel match) in the correspondig row(+1 in the right) of the match in the google spreadsheet. And that for each match between the excel and the google spreadsheet.

Thanks in advance for your help, you will save my life guys.

Vaha


#2

Hi,

@Vaha read the excel sheet first and store it in one datatable(dt1) and read the Google spreadsheet and store it in another table(dt2).

Add one more column in dt1(Matched column).

Using for Loop or linq get the matched row and update ‘matched’ in matched column in dt1.

Using write range activity write the dt1.

Regards,
Arivu


#3

Thx a lot Arivu, i try that and i let you informed.

Vaha


#4

Hello everyone,

After your message @arivu96, I tried something like that. I changed one thing :

  • I try to match the correspondance between two excel column now (it’s easier). “ID number 1” and “ID Number 2”.
    So, what i want to do is :
  • Read the cell B2 of Merch 1.xlsx
  • Compare to B Column of Merch 2.xlsx
  • If match (or approximative match = the “444” ID with the “444 (454)” must be matched) then,
  • Write B row of Merch 1.xlsx in Res.xlsx
  • If not, do nothing
  • Then, start again for C2 of Merch 1.xlsx and so on for all the rows.

So, concretely, put similar “ID” in one unique Excel.

Could you help me please. @certified

Thx by advance guys.
Vaha

Main.xaml (16.9 KB)
Merch 1.xlsx (8.1 KB)
Merch 2.xlsx (7.9 KB)
project.json (240 Bytes)


#5

Hi @Vaha,

i have modified your code, can you check it

Merch 1.xlsx (8.2 KB)
Main.xaml (16.2 KB)
project.json (240 Bytes)
Merch 2.xlsx (7.9 KB)
res.xlsx (9.3 KB)

Regards,
Arivu


#6

Thanks a lot @arivu96 !

It works like a charm.


#7

Ha ha ha ha :grinning:
You are welcome @Vaha,

Regards,
Arivu


#8

Hi @arivu96 …I have 2 excel and they both have date column in them…if the date matches then i need to write the rows from first sheet to the second…Currently I have first sheet as data row as i had to filter…I am reading range from Second sheet and storing as datatable…Can you please help?


#9