Excel : search a value and retrieve data

Help
, ,
1

Hello Community,

Is it possible to retrieve in Excel sheet particular data and then select the cell value just below this particular data?
I searched in the forum but all solution doesnt work with my case.

In my case I would like to search the word “EAN” which his position is random in the sheet. Then retrieve the value in the cell below (+1 cell).

Thanks for your help

1 Like
2

yah thats possible
–use a excel application scope and pass the file path as input
–inside the scope use a read range activity and get the output with a variable of type datatable named dt
–now use a for each row activity and pass the dt variable as input
–inside the loop use a IF condition like this
Not dt.Rows.IndexOf(row).Equals(dt.Rows.Count-1) AND row(“yourcolumnname”).ToString.ToUpper.Contains(“EAN”)
if true it will go to THEN part where we can use a assign activity like this
str_output = dt.Rows(dt.Rows.IndexOf(row)+1)(“your columnname”).ToString

so the output variable str_output a string variable wil hold the value we need

hope this would help you
Cheers @jawahar

2 Likes
3

@Palaniyappan
Thanks a lot for your reply, I got one errror “the colum “EAN” doesnt exist in datatable”
EAN is actually a column name and could be either in E1, E2 F1 or F2;
When it is in E2 for example it meens that the first line is empty,
I am currently trying to detect that and if it is empty, before to search “EAN” delete the first row.
image

4

@jawahar

Place a breakpoint after you read range from your excel and check the column names.
Take note that your Headers are in row 2 and not row 1.

1 Like
5

Hi @jawahar,

you can using the lookup activity. Through lookup activity you can able to retrieve position of the word you want to search. And then +1 in the position for the further processing.

Happy Learning :slight_smile:
Regards,
Achal Sharma

2 Likes
6

@Palaniyappan @Achal_Sharma @JGuarino

Thank you so much for your help, I made a mix of all your proposition and at the end I used split method in order to convert position cell into integer like F keep his string type, and 2 like integer , then I make 2 +1 for the final result F3 :slight_smile:

image
image386×617 14 KB

2 Likes
7

Hi @jawahar,

You can use the find activity from the below one.

Regards
Balamurugan.S