In Datatable if there is duplicated column data, how to add index number

[sK4ri (Karoly Simon)]

Hi,

I have a datatable: if in a column (that contains reportnames) there are duplicated entries, I want to add a counter/index number to those entries.
How should I do it?

For example:
As Is:
ColReportname
ReportnameABC
ReportnameABC
ReportnameBCA
ReportnameCAB

To Be:
ColReportname
ReportnameABC_1
ReportnameABC_2
ReportnameBCA
ReportnameCAB

Thank you guys in advance!

[Anil_G (Anil Gorthi)]

@sK4ri

Please try this in invoke code…dt is added as argument with in/out

dt.AsEnumerable.GroupBy(function(x) x("ID").ToString).Where(function(x) x.Count>1).ToList.ForEach(Sub(r) r.ToList.ForEach(sub(p) p("ID") = p("ID").ToString + (r.ToList.indexOf(p)+1).tostring))

Input Table:

Invoke code

OutputTable:

cheers

[Nitya1 (Nitya Tomar)]

Hi @sK4ri

In UiPath, you can achieve this by using a combination of activities to loop through the datatable and add a counter/index to duplicate entries in the ‘ColReportname’ column. Here’s a high-level description of the steps to accomplish this:

1. Read the DataTable: Use the “Read Range” activity to read your DataTable into a UiPath DataTable variable.

2. Initialize Variables: Create variables to keep track of the counts for each unique report name and a variable to store the modified DataTable.

3. For Each Row: Use a “For Each Row” activity to iterate through the rows of your DataTable.

4. Check for Duplicates: Inside the loop, check if the current row’s ‘ColReportname’ value is already in the dictionary of counts. If it is, increment the count and update the ‘ColReportname’ value with the counter/index.

5. Update DataTable: Update the current row of the DataTable with the modified ‘ColReportname’ value.

6. Write DataTable: After processing all rows, use the “Write Range” activity to write the modified DataTable back to your data source.

Here’s a UiPath workflow in pseudocode:

- Read Range: Read your DataTable into a UiPath DataTable variable (e.g., dtInput).

- Initialize Variables:
  - Create a dictionary (e.g., dictReportCounts) to store counts for each unique report name.
  - Create a new DataTable (e.g., dtOutput) with the same structure as dtInput.

- For Each Row (Row in dtInput):
  - Get the 'ColReportname' value from the current row.
  - Check if the value is in dictReportCounts.
    - If yes, increment the count and update the 'ColReportname' value.
    - If no, add it to dictReportCounts with a count of 1.
  - Update the current row in dtOutput with the modified 'ColReportname' value.

- Write Range: Write dtOutput back to your data source.

- End of Workflow

This pseudocode outlines the high-level steps you should follow in UiPath to achieve your desired outcome of adding a counter/index to duplicate entries in the ‘ColReportname’ column of your datatable. You’ll need to create the workflow based on this pseudocode using UiPath activities and variables.

Thanks!!

[sK4ri (Karoly Simon)]

Thank you both for the fast and detailed ideas! I went with the more compact approach and marked it as solution!