How to group rows that share similar information into one row

Hello everyone,

I have a small sample excel data table that contains duplicated values that I need to combine into single row.

Initial table:


The highlighted rows are the duplicated rows that I want to combine. (Here is my initial table Sample Data.xlsx (8.8 KB) )

So the above table should look like this:


Product No and Date Issued columns have unique value and i would like to retain the information when combining the rows.

Is there any way to accomplish this with UIPath?

Below is a rough simplified table example of what I’m trying to achieve:

Step:1
=======
| A1 | a | raccoon
----------------
| B2 | b | dog
----------------
| B2 | c | tiger
----------------
| B2 | c | lion
----------------
| A1 | d | deer
----------------
| A1 | a | bear
----------------

Step:2
=======
| A1 | a | raccoon; bear
---------------------
| B2 | b | dog
---------------------
| B2 | c | tiger; lion
---------------------
| A1 | d | deer
---------------------

I would be greatly appreciate if anyone could help me with this, I am still quite new to UIPath.

Thank you,
Hariz D

Hello @harizd,

Here are 2 different to help you eliminate duplicate rows from a DataTable:

  1. Since version 2018.4.1 UiPath contains a dedicated activity to delete duplicate rows in a DataTable. Input is a DataTable, and output is the same DataTable without the duplicate rows.
    image

  2. If not available, another way is transform your DataTable to a DataView and back to a DataTable.
    The following is the precise code you will require to do so:

DT = DT.DefaultView.ToTable(True, New String() {“Job No”, “Garment No”, “Brand”, “Color Code”, “Qty”, “Color Description”, “Product No”, “Date Issued”})

image

2 things you should notice: The first parameter in ToTable has to be True. This tells UiPath to return the unique rows from the table. The second thing is that if you have to list all of the column names inside a String Array: id this is not done, the return DataTable will not have the columns that were not included in the String Array.

1 Like

Hi @juan.lengyel

Thank you for your guidance. I will try this the soonest and will tell you how it goes.

Okay, so I tried the first method using Remove Duplicate Rows activity but there are no difference in the output.

Here’s the data comparison side by side of the original file (right side) and output file (left side):

I also tried using the second method with Assign Activity but I also got the same result as before with no changes.

Could you check my workflow if I am actually doing this correctly?
Combining_Rows.zip (24.3 KB)

Should I go with VBA Excel for this problem or is there a way to solve this with UIPath?

@harizd
give a try with this xaml Main.xaml (11.0 KB)
you can make changes in xaml according to your need

content of xaml:

dt_old >> old DataTable

new datatable dt
dt.Columns.Add("Col1", GetType(String))
dt.Columns.Add("Col2", GetType(String))
dt.Columns.Add("Col3", GetType(String))

dt = (From p In dt_old.AsEnumerable()
Group By x= New With { Key.a =p.Item(“Col1”),Key.b=p.Item(“Col2”)}
Into Grp = Group Select dt.LoadDataRow (New Object() {grp(0)(0), grp(0)(1),String.Join(";",grp.select(Function(c) c("Col3")))},False)).CopyToDataTable
4 Likes

Thank you so much for this @AkshaySandhu!

May I know what does this particular line of code do?

Into Grp = Group Select dt.LoadDataRow (New Object() {grp(0)(0), grp(0)(1),String.Join(";",grp.select(Function(c) c("Col3")))},False)).CopyToDataTable

what does the grp(0)(0), grp(0)(1) do and also what is Function(c )?

Someone please correct me if I am wrong…

@harizd I am not good at explaining things but I gave a try below…

Out linq query can explained as below:

(From p In dt_old.AsEnumerable()
–here we are creating enumerable from dt_Old

Group By x= New With { Key.a =p.Item(“Col1”),Key.b=p.Item(“Col2”)} Into Grp = Group
– In this line we are grouping the datarows in groups (grp) based on Col1 and Col2 [in our case we are getting 3 groups, each containing 2,1,2 datarows respectively]

grp(0)(0)= from a group of datarow i.e grp, get first row’s first column’s value

grp.select(Function(c ) c(“Col3”))) = here we used function because in function we pass an argument and in return we get value. Here argument is datarow and value is value of Column “Col3”.
You can name your functin anything you want like abc or xyz or just a or just b etc.

Select dt.LoadDataRow (New Object() {grp(0)(0), grp(0)(1).String.Join(";",grp.select(Function(c ) c(“Col3”)))},False)).CopyToDataTable
– In this line we are doing three things

  1. creating the new array of objects ie <<New Object() {grp(0)(0), grp(0)(1),String.Join(";",grp.select(Function(c ) c(“Col3”)))}>>
  2. Loading that array of object in new datarow in dt with dt.LoadDataRow
  3. selecting that datatable << dt >> as enumerable of datarow so that at the end we will get the datatable by using CopyToDataTable method

I hope I am clear enough…

3 Likes

I see, thank you for the detailed explanation :grin: I understand now

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