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

Help Activities
, ,
1

Hii,

I have a column like these:

col1

xyz
xyz
xyz
xyz
xyz
xyz
xyz
pqr
pqr
pqr
mno
mno

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

2

@Doonline

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.

3

expected output after merging will be
xyz

pqr

mno

4

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?

5

i want to merge the column not to remove duplicates

6

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

7

@Doonline

  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

8

Hey @Doonline !! Lets go to the solution!

  • Step 1: I will use this example file.

    image
    image1920×1019 84.9 KB

  • 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.

    image
    image1920×540 56.4 KB

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

    image
    image1069×765 19 KB

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

    image
    image1920×694 66.5 KB

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

(From p In dt_test.AsEnumerable()
Group By
col1=p("Code").ToString
Into Group
Select dt_grouped.Rows.Add({
	col1,Group.Count()
	})).CopyToDatatable

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

image
image1920×980 80.3 KB

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
9

I just need to merge the cells of the particular value

10

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!