Concatenate column values where duplicates using linq

Help Activities
, , ,
2

@qwerty1,

Follow this approach:

Full Solution:

This image is a flowchart of an automation process in UiPath that reads an Excel workbook, groups data, iterates over the groups to concatenate values and add rows to an output table, writes the output to a new worksheet, and logs a message once done. (Captioned by AI)
This image is a flowchart of an automation process in UiPath that reads an Excel workbook, groups data, iterates over the groups to concatenate values and add rows to an output table, writes the output to a new worksheet, and logs a message once done. (Captioned by AI)715×1444 69.6 KB

Variables:

The image depicts a variable management window showing three variables named dtInput, groupedData, and dtOutput, all scoped to DT_Test, with their types being DataTable and List<IGrouping<String, DataRow>> respectively. (Captioned by AI)
The image depicts a variable management window showing three variables named dtInput, groupedData, and dtOutput, all scoped to DT_Test, with their types being DataTable and List<IGrouping<String, DataRow>> respectively. (Captioned by AI)933×321 13.6 KB

groupedData LINQ:

dtInput.AsEnumerable().GroupBy(Function(row) row("ID").ToString()).ToList()

Concat LINQ:

String.Join(" ", currentItem.Select(Function(row) row("Locale").ToString() & row("Number").ToString()))

Add Data Row–> Array Row:

New Object(){strValue,"J",currentItem.Key,currentItem.First()("Date"),currentItem.First()("Area")}

Input/Output:

The image displays two tables, dtInput and dtOutput, each containing columns for Number, Locale, ID, Date, and Area, with dtOutput showing a transformation including a column concatenation from dtInput. (Captioned by AI)
The image displays two tables, dtInput and dtOutput, each containing columns for Number, Locale, ID, Date, and Area, with dtOutput showing a transformation including a column concatenation from dtInput. (Captioned by AI)367×338 5.68 KB

The image shows an Excel spreadsheet named "Sheet1" containing columns labeled "Number," "Locale," "ID," "Date," and "Area," with several rows of data filled in under these headings. (Captioned by AI)
The image shows an Excel spreadsheet named "Sheet1" containing columns labeled "Number," "Locale," "ID," "Date," and "Area," with several rows of data filled in under these headings. (Captioned by AI)344×574 9.53 KB

A spreadsheet titled "Output" contains a table with columns for Number, Locale, ID, Date, and Area, filled with data in two rows under each heading. (Captioned by AI)
A spreadsheet titled "Output" contains a table with columns for Number, Locale, ID, Date, and Area, filled with data in two rows under each heading. (Captioned by AI)478×579 6.96 KB

This is a working solution. You can always make it more refine.

Thanks,
Ashok :slight_smile:

1 Like