Search for a value in another excel

Hello everyone,

I have the following scenario:
Excel 1:
Column A: Project Name
Columm B: Project Code

Excel 2:
Column A: Project Name
Column B: Project Code

However, the “Project Name” for Excel 2 is always empty. So I would like to search for Project Code into Excel 1 and then get the Project Name from Excel 1 and fill on the Excel 2.

I know it’s can be hard to understand so I put in a “paper” to be understandable.

image

Hey @rafael.oliveira
Check out this video it will help you: https://youtu.be/_SHU8uTVvaE

Cheers,
Parth

Hi @rafael.oliveira,

To search inside the sheet. you can use the find activity in the below. It will return the cell info (it is a string array) like “A32,B45” , And also it will return the row index (it is an integer array ) like “32,45”.

Video :

Thank you
Balamurugan.S

hi @rafael.oliveira,

it is a very easy one buddy, let me know if you found any solution as of now or i will provide you a xaml for that if you haven;t found any soln.

Cheers,
Shikhar

Not yet, still trying to figure out a way. Maybe if you can give me some more details.

Read both sheets as DTs
Use the lookup activity to look up DT1 value based on the code in DT2,

Hi @rafael.oliveira,

BlankProcess4.zip (41.0 KB)

Try this and let me know if this works :slight_smile:
Cheers

Your approach is good, however, if I have a list of 3000 Projects, it will take a lot of time.

It worked that way, however, what if I have two Project Code with the same code? It will look up only once.

It worked that way, however, what if I have two Project Code with the same code? It will look up only once.

Is there only one project name to one project code in DT1? If so then, it should not matter. For each row in DTExcel2 lookup code in DT1, return name. write return to DT2. The write DT2 to new excel file.
If you have duplicates then you could remove duplicates in DT1 if that is causing a problem.

Yes the first occurence.
Please mark it as solution if it helped.