Filter and update the excel data

Help
1

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.

2

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.
3

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

4

You can also write Select query instead of loop.

5

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
6

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

regards
venkat

7

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.

8

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.

9

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.

10

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