Search and enter on a particular column

[balkishan (Balkishan Singh)]

Hi, I want to find the keyword in the excel (keyword is my ID) where from the particular column, Like My column first is ID and I once find I want to make an entry in the excel in the status column.
My Excel Structure

Here suppose I search ID 1004 so I want to write in the Status column
@Palaniyappan @lakshman @Lahiru.Fernando @amarasto @aman_sheik plz help.

[Palaniyappan (Palaniyappan P)]

Fine @balkishan
–use excel application scope and pass the file path as input
–use read range activity and get the output as datatable variable named outdt
–use for each row loop and pass the above variable as input
–use if condition inside the for each row loop and mention the condition like this
row(0).ToString.Contains(1004")
and if this condition passes it will go to THEN part of if condition where we can use
assign activity like this
row(2) = “Yes/No”
so once after processing all the rows
–next to this for each row loop use a kill process with process name as “EXCEL”
–then use write range workbook activity where mention the file path of the excel with the sheet name and enable the Add headers property and mention the range as “A1” and datatable as outdt as it has the updated status column with values in it

So this would work for sure buddy @balkishan
Kindly try this and let know for any queries or clarification
Cheers

[balkishan (Balkishan Singh)]

Bro Already done that step, but que is how to search and I want to increment it also.

Like how to search the ID in the column and write in the Status column Yes/No
@Palaniyappan
@KarthikByggari

Bro ID will come random. like I am using the IF condition and checking the eligibility. Like After checking everything the ID person 1003 not eligilbe then find the id 1003 in the excel and write in the Status column No

[HareeshMR (Hareesh Madasi)]

I hope you need to do it for more than one ID.

So, Here are some steps @balkishan,

1. Add all the unique ID’s for those you want to update in excel into an array assume IDArray
2. Then use for each row to loop through the excel data datatable.
3. Then use if condition like IDArray.contains(row(“Unique ID”).tostring)
4. Update the status using write cell… if exists–> in then condition write yes else no

[Palaniyappan (Palaniyappan P)]

Fine,
in that case
kindly follow the below steps

–use excel application scope and pass the file path as input
–use read range activity and get the output as datatable variable named outdt
–hope you store the value of that random value of 1004 to a variable of type string named in_value
–use for each row loop and pass the above variable outdt as input
–use if condition inside the for each row loop and mention the condition like this
row(0).ToString.Contains(in_value)
and if this condition passes it will go to THEN part of if condition where we can use
write cell activity like this
mention the sheet name and range as “C”+(outdt.Rows.Indexof(row)+2).ToString
and mention the value you want to write
Thats all buddy
Cheers @balkishan

[KarthikByggari]

Another way to achieve your requirement is

1. Use Lookup range activity to find the ID address.

LookUp Range

You can find the example workflow in the given link.

For example, if you search for 1003, the activity returns “A4”

2. From the found address, extract only number and use write cell activity with an address “C” & foundCellNumber.ToString

Regards,
Karthik Byggari

[Palaniyappan (Palaniyappan P)]

it should be like this buddy @balkishan
just now modified the previous comment sorry for that
“C”+(ExcelData.Rows.Indexof(row)+2).ToString

and its Indexof
not Just Index
@balkishan

[Palaniyappan (Palaniyappan P)]

“C”+(ExcelData.Rows.Indexof(row)+2).ToString

Indexof buddy not Index

@balkishan

[balkishan (Balkishan Singh)]

Okay fine, should I give a A1 or not bro

If you see here I have to write Yes/No so where to give this value bro?

[Palaniyappan (Palaniyappan P)]

wait wait wait…
buddy this “C”+ (ExcelData.Rows.Index(row)+2).ToString should be there instead of “A1” as range and we need to mention the value we want to mention in the status column in the value property

sorry if i have meant that wrongly

Cheers @balkishan

[balkishan (Balkishan Singh)]

@Palaniyappan

“C”+ (ExcelData.Rows.Index(row)+2).ToString

No problem bro.

[balkishan (Balkishan Singh)]

Thanks @Palaniyappan @HareeshMR @KarthikByggari it’s resolved.

[Palaniyappan (Palaniyappan P)]

Cheers
@balkishan

[balkishan (Balkishan Singh)]

@KarthikByggari

From the found address, extract only number and use write cell activity with an address “C” & foundCellNumber.ToString

Hi, I want to know about this method also. can you plz guide me.
i followed the steps as you mentioned.
I got the address is A4 when I search 1003. So next I am using the write cell activity. but it’s not writing.
I gave the condition like this.
c is my status header where I want to write.
SearchResult is LookUp Range variable

@KarthikByggari

[KarthikByggari]

You did right except one thing.

You have to extract the number from the searchResult.

resultString = Regex.Match(searchResult, @"\d+").Value

you have to use this resultString in the write cell activity.

"C" & resultString.ToString

Regards,
Karthik Byggari

[balkishan (Balkishan Singh)]

some expression error @KarthikByggari
why @ bro in the query we need only number na? right me if I am wrong.

[KarthikByggari]

Remove @

I gave you c# expression.

[balkishan (Balkishan Singh)]

still it’s showing an error after remove of @ KarthikByggari

[KarthikByggari]

Please add the following namespace.

System.Text.RegularExpressions

[KarthikByggari]

Click on Imports in the panel (bottom) and add it as shown in the figure.