Updating excel sheet for repeated row values

Hello Everyone,

I need some help in updating an excel worksheet.

Here is the scenario:

Column A Column B
A76480-112
C84600-102
A76480-112
C84600-102
A76481-112
C84601-102
A76481-112
C84601-102
A76480-112
C84600-102
A76480-112
C84600-102
A76481-112
C84601-102
A76481-112
C84601-102
A76480-112
E68235-100

I have got 2 columns where values in column A are input values to an application and column B will be updated with an output value from the application. Some of the values in column A are repeated, however, their corresponding output values are different.

I am trying to use Write Cell to update the Column B values. However, since the values in Column A are repeated, I am unable to update the correct value in the corresponding cell.

What I know is that I need to pass the row index while looping it For Each, what I don’t know is how to pass the index to the Write Cell for it to update corresponding cell.

from the sample data not all is clear but in general you can doit with grouping the data on first col and memorizing the row index

with following Flow / LINQ you can also create a helper structure.

  • readin your excel with a read range - dtData
  • prepare the the helper datatable by:
    dtHelper = dtData.Clone
  • add a Datacolumn on the end: ColumnName = idx
  • Assign activity and a LINQ / or For each row on dtData - Fillup dtResult along with rowindex info

Grouping the data:
Assign activity:
Left: Result | DataType: List(Of List(Of DataRow)) - outerList: the groups, inner List: the group members
Right:
(From d in dtHelper.AsEnumerable
Group d by k=d(0).toString.Trim into grp=Group
Select g=grp.ToList)

Now you can iterate over groups by a for each activity | TypeArgument is: List(Of DataRow)
Within the Body Block you can do the corrections / processings on the group & members
With the help of the idx Column you can access the data in dtData or can use it for updating in Excel.

We can suggest to do the entire updates on dtData and then overwrite /write to a new Sheet to Excel

Hi

Welcome back to uipath forum

Hope the below expression would help you resolve this

Inside the for each row loop and In write cell mention like this for row index in CELL PROPERTY

“B”+ (dt.Rows.IndexOf(row)+2).ToString

Cheers @rohankulkarni

Hi,

Hope the following sample helps you.

Sample20211014-1.zip (18.3 KB)

Regards,

In the write cell I am using “B”+ var_Int_RowCount.ToString to iterate through exact row numbers in the datatable. When I tried to pass the index as well, it failed to update the excel sheet

Hmm it should work

Is the value passed in it

Can we see the activity a screenshot if possible

@rohankulkarni

Pls see below:

image

Please mention like this if you have your datatable stored in a variable named dt or change the name of the datatable in the below expression as per yours

The reason is you have used both rowcount and index
That’s why cell position got differed

Use the above expression and give a try

@rohankulkarni

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