Search in excel with like %%

[Aguirre]

Hi.
I have a list with more than 1000 records and 10 columns, I need to search for a value in a cell and extract some data from that row, and then copy it to a new excel. I find two problems:
one.-
The problem is that I am using “Lookup data table” and it only finds the exact data, but I need to search with a LIKE %%, since that search data varies although it always starts with “SAPA”, I have carried out several tests and none it works.

2.- I need to go through the entire list, I have tried with “for each row” but it always returns the first row.

The following example works, searching for the exact word:

Thaks.

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Hi @Aguirre ,

Lookup datatable provides the result for first occurance of your search and it will not provide the entire result of search. For your requirement you have to go for filter data table and use your search key on the specified column to get the result and you could store the result in another datatable and store this data to another sheet with write range activity.thanks.

https://docs.uipath.com/activities/docs/filter-data-table

[Robinnavinraj_S]

Hi @Aguirre

Need a clarification

your destination cell remain on same column? or it will be on any column ?

Thanks
Robin

[liu_shubin (Shubin)]

A simple way is to use Select instead of using lookup datatable
InputDT.Select(“name like ‘%John%’”).CopyToDataTable

Here “name” is column name. The command will select all rows containing ‘John’ in the ‘name’ column and get back all filtered rows with all the columns.

[Aguirre]

@liu_shubin

%John%’”

Hello.
I have tried what you tell me, but I can’t get it to filter, I’ll put a screenshot in case you find my mistake.
I have tried using “Contains” putting “SAPA” and “%SAPA%”, but it creates an empty sheet.

[Aguirre]

@Robinnavinraj_S

Need a clarification

your destination cell remain on same column? or it will be on any column ?

Thanks
Robin

Hello
The idea is to copy several filtered cells and bring them to a new sheet. The cells will always have the same order.
Thank you.

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Hi @Aguirre ,

I think you are using in correct way but not sure what is the issue. Could you pls share the sample input excel i will also try at my end and let you know. thanks.

[Aguirre]

@kirankumar.mahanthi1

I think you are using in correct way but not sure what is the issue. Could you pls share the sample input excel i will also try at my end and let you know. thanks.

Hello.
I was preparing an example to upload it here, and it has worked for me, with what used to be some small bug that I didn’t see.
Thank you very much for the help, I have succeeded.
I put your solution as the correct one.
Thank you all.

All the best.

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Glad to hear that you have succeeded. nice connecting with you . Thanks.