How to merge the column if columns contain multiple values and the range size is nnot fixed


I have a column like these:



I want to merge the similar values, But it should merged dynamically as these values are changing for other files and also these range can also be differ for other files.

Please Help ASAP


Sorry didn’t get you. Can you please show us expected output you are looking here. So that we can check and help you better.

expected output after merging will be



Hey @Doonline!! Do you have other columns you would like to ‘group by’ with the value or do you just want to remove the duplicates?

i want to merge the column not to remove duplicates

i need to merge the all the xyz then merge all pgr then merge mno


  1. First use Read Range activity to read the data from excel file and will give output as DataTable. Let’s say dtInput.

  2. And then use below Linq query.

( From row in dtInput Group row by a = row("col1").ToString.Trim into grp = Group Where grp.Count > 1 Select grp.ToList ).SelectMany(Function(x) x).CopyToDataTable

Hey @Doonline !! Lets go to the solution!

  • Step 1: I will use this example file.

  • Step 2: Let’s create our table that will contain the result, that’s because in our query, we’re going to add row by row. We will then use the “Build Datatable” activity.

  • Let’s go to the structure of this table.

  • Step 3: Let’s read the table and store it in a variable of type datatable “dt_test”.

  • Step 4: Now we are going to use the following query

(From p In dt_test.AsEnumerable()
Group By
Into Group
Select dt_grouped.Rows.Add({

and store in the datatable variable that we created in the “Build Datatable” activity.

Since you wanted an ASAP result, I won’t go into the Query explanation in depth. The result follows directly. If you want an explanation about the query, let us know here. I hope it helped you!

The .xaml.
Main.xaml (10.6 KB)

The .xlsx.
Report.xlsx (11.4 KB)

1 Like

I just need to merge the cells of the particular value

Ok @Doonline! So you can add an assign activity after the “Group By” assign and before the “Write range”.

dt_grouped = (From r In dt_grouped.Select() 
Where r("Code").Equals("xyz")
Select r).CopyToDataTable

Where the String “xyz” is, pass its specific and dynamic value.

Hope it helps!