Filter datatable and write cell

Hi All,

Need some help.
I have a keyword stored into a variable.i want to search that keyword into the excel datatable row.
Onces entry is found then write into column 1.

Currently i am using if condition for comparing keyword with row(2). But dt has 2000+ item so it taking time .i need any efficient way.


Have you tried using Lookup Data Table activity?

It works like Excel VLOOKUP formula, but it will also return you the index of the row where the match was found.


Thanks ,I haven’t tried with Lookup dt …i will try and get back to you.

1 Like

Use select method to filter your datatable
DtRowArray ="[column name] = ‘keyword’ ")

This will give you an array of datarows

Now use for each activity

For each of DataRowArray

Inside for each body use an assign activity to get the row number of that datarow in main Datatable

Rownumber = MainDatatable.rows.index(DataRowArray(itemIndex))

This will give you the row number of that row in main table

Now use write cell to write the value in 1st column of that row

1 Like

LOOKUP DATATABLE activity will work much faster in this case
So ensure that lookup datatable activity has these values in its property panel
Input value = Yourvariable.ToString
Input Datatable activity = dt
Lookup column index = 2 (as you are checking with row(2) )
Target column index = column index to want check with (usually it starts from 0 for the first column)
Result = str_output

Now inside the FOR EACH ROW loop next to this Lookuo datatable activity Use a assign activity like this
row(“yourcolumnname”) = str_output.ToString

This will update that value in the column you want

Hope this would help you


I don’t understand the logic
However you are using Lookup datatable inside for each row loop
If it is having 2000 rows , it is also a time taking practice, how it is better than filtering the datatable where it filters the rows which are having only that value (may be the filtered rows comes around 1000 records less or more but less than actual records )

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