Filter and update the excel data

I am having excel sheet with the following data
excel%20data .

My requirement is I want filter the column “Dept No” and extract the data and print each"Emp No" based on filtered data of “Dept No” and update the status(Yes/No) to the “Processed(Yes/No)” column at the same time.

Thank you in advance.

first read this excel take data in data table

  1. use for each row
    in if condition (row.item(“Dept No”).tostring==“1”)
    2.output either you can have one more different data table/you can write that to excel directly along with status.

Thank you for the response.
As I have attached sample data,in real I am having thousands of records. Reading all the data and looping it with a condition which I assume it is a time taking process,so that is the reason which I want to extract the data after filtering. I want to update the status in the same sheet. Whether if filter is applied directly on the sheet ,we have to remove the filter after processing all the rows

You can also write Select query instead of loop.

If you want to filter the table before you get the data table from the sheet, you’ll need to filter it in the excel sheet itself. Instead of that, do this:

  • read the entire table using read range.
  • say your datatable variable is dtTable, dtTable.Select("[Dept No]=‘1’") THIS will give you a filtered array of ONLY those rows where Dept No is equal to “1”
  • Use a for each activity and loop through the filtered data. Update the “Processed(Yes/No)” value.
1 Like

hello @nagarajunani you can use filter datatable activity after getting the data into data table u can use this activity then put ur filter condition there u will get the filtered data table . then u can loop through that table u can update ur satus there. i think it will help u.
note: this activity available 2018.2 version


How to update “Processed(Yes/No)” value in the sheet. I am able to update in datatable but not in the sheet. I request to build a sample xaml file as I am new to it.

If you’re able to update your datatable with the right values, ll you have to do is overwrite your original datatable with the new datatable. Use Write Range.

Thank you for the update. Actually now I want to retrieve the data rows based on the Maximum value of the “Dept No” column. For example,In the above screenshot,the maximum value of “dept No” column is 3. Now I want to retrieve all rows of dept no 3.

Thank you in advance.

dt.AsEnumerable().Max(Function(row) row(“colname”))