How to match two row.item in different Excels

there is a scenario where we need to match the row item with the other row item which contains the it in the narration part
row instrument no in first excel
to match with narration part in another which contains

*** it needs to be done for all the line items in the excels

How is format of the excel like instrument no and narration part are columns in same excel


Nived N :robot:

no they are in different excels in different column

Can u show the excel file of both as sample?

After matching what u want to do in final @mallanagoud017

after matching we need to set status as matched by adding new column
i able to do this if there was only the instrument number also present but the problem is the unstructured data in narration column

Can u explain the complete scenario what u want to do so that we can design process for it

xamltry xamltry1
i have done this but not working

i want to updated status as matched if the instrument no in first excel column matches with the narration column in the different excel column which contains the column item as

In which excel sheet? U want to update

the first Excel which contains the instrument no
the only concern was how to get the instrument no from the unstructred narration column

First create a column named status in the excel sheet of instrument no to write the match or unmatched ( I am considering column is in C column)

For that

first read the excel file having instrument no as store in datatable dt1,

Then read another excel file having narration and store in datatable dt2

create a integer variable index and assign it’s value as 2

Use for each loop to loop through all values of dt1 ( for each row in dt1)

Inside this loop use another for each loop to loop through all the value of dt2 ( for each row1 in dt2)
Inside it use the if condition, with condition as row1(“Narration”).ToString.Contains(row(“Instrument No”).ToString)

If the condition is true then in then section, use write cell activitiy to write the as matched in status column, with cell value as “C”+ index.ToString, with value provided as " Matched"

Below it assign as index = index+1

If the condition is false
In else section, Then below it , increment the index no by one

Then below it use continue activitiy so that it continues to next loop

In this way u can do this

Hope it helps
Nived N :robot:

Happy Automation :slight_smile::slight_smile::slight_smile:

Thanks for the solution


can i know how to fetch the instrument no from the narration and then match it,…
it will be helpful for learning how to do.

row1(“Narration”).ToString.Contains(row(“Instrument No”).ToString) , this condition will check it

Of it is true , it true it will update the status sheet as “Matched”

If it is false, it will continue to next row