How to find the cell value in the excel

I need the cell value in the excel for the particular value in one column but it contains duplicate values also
like this sheet

If you want to get cell for any respective value then use Lookup activity.
Read Range:

Use the Read Range activity to read the Excel sheet into a DataTable.
For Each Row:

Use the For Each Row activity to iterate through each row in the DataTable.
If Activity:

Inside the loop, use an If activity to check if the current row’s value in the specified column matches the value you are looking for.
Assign Activity:

If the condition is true, use an Assign activity to assign the corresponding value from another column to a variable.
Break Activity (Optional):

If you only want the first occurrence, use a Break activity to exit the loop after finding the first match.

@prasanthmuthu1997 a(3).ToString.trim).Where(Function(b) b.ToString.trim.Equals("Scientist")).tolist

gives you the output as list

Is this you are expecting?

Hi @prasanthmuthu1997 ,
You need get cell index or value true/false when search value

Might this video will help you.

cell index like C2, C5

Hi @prasanthmuthu1997 ,

Get excel column position based on Excel column name

  • this will be useful when someone want to write in excel, each cell one by one in a column
  • for that first read the excel with read range activity and get output as dt
  • now to get any column position like “A1” or AA1”

For “A1”, “A2”, “A3”,……

use this expression in Cell Range property of Write cell activity kept inside a For each row activity

Convert.ToChar(65 + dt.Columns.IndexOf("Columnname") ).ToString + (dt.Rows.IndexOf(CurrentRow)+2).ToString

(Output will be “A1”, “A2”,…. String type)

As we are inside the for each row loop, CurrentRow will increment automatically and thus write cell activity will write the value in each cell one by one in a particular column. That column is mentioned with column name.
2 in rowindex is because index starts with 0 so 0+2 will be A2, so that bot starts to write from A2 as A1 is header in excel
If there is no header at all then mention as +1 instead of +2

65 usually denotes “A” - ASCII code
Converting that to Char will give the equivalent alphabet

For AA1, AA2, AA3,……

Same with a little change in expression

Convert.ToChar( (65 + dt.Columns.IndexOf("Columnname") ) - 26).ToString + (dt.Rows.IndexOf(CurrentRow)+2).ToString

(Output will be “AA1”, “AA2”,…. String type)

My sample


my source (8.9 KB)


This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.