How to update EXCEL rows where id is occurs more then 1 time

Help Studio
1

I have an excel sheet with more then 3000 rows
Where Username exist more then 1 time and ID is different. I have to read each time that person and after performing my task would update the status on perticular row.
How can i do that?

Forum Engagement Daily Reports
2

@Ellen

If you are reading the data in loop then in same look simply by using an asisgnn we cna update or can use a write cell based on the loop counter or using index in for loop properties

Cheers

3

I have done like this…
Using - Read rang workbook to read the Excel sheet - TestDT
Then using - Filtere DataTable to filter by Name so that I only have perticular data I need to work with.

  • TEST DT after its still TEST DT
    Then im doing a loop on TEST DT running my process and doing what i have to do…
    After completing that I need to Right a Status and when i use assign activity and doing Write rang its over writing my first 2 column data…
4

@Ellen

As the data is already filtered…and you cant find unique row just by name…you need to go by creating a rowindex before doing filter datatable ao that in the loop we can use write cell activity and use the rowindex of currentrow that is saved to get the row number in excel

To create a row index add a nee column to thw datatable after read range say index and then use the following invoke code by passing dt as in/out argument

Dt.AsEnumerable.ToList.ForEach(sub(r) r("index") = dt.Rows.indexOf(r).ToString)

Cheers

5

sorry but I didt understood that…

my issue when i use update row item and then i use write rang workbook in for each loop its remove my header of sheet and also alle rows.
:frowning:

6

@Ellen

It would remove like that…as the data would be over written

Update row item will reflect in datatable and not on excel

Cheers

7

how can i both filter the table and run for each on data and update datatabe of current record?

8

@Ellen,
.
If you want to use Excel only, you can use Activities - Find/Replace Value

If you can read the data into DataTable and do the modification in it and write it back to Excel.

This will also work using Write Range activity. Just mark Add Headers property.

Thanks,
Ashok :slight_smile: