Combine Duplicate row value into Single row

Help Studio
1

image

I have an excel which is having duplicate rows like in the screenshot. I need to convert this into data table and the all 4 values are combined into a single rows value.
How we can achieve this??

2

@agathiyanv please show output template as well

3

I need those rows in this format

image
image885Ă—37 2.48 KB

4

Hey @agathiyanv,

You can pivot the datatable to get the desired result as the linq pivot will use the Group By method. For reference have a look on the video.

Thanks,
Sanjit

5

ok wait please

6

Hey @agathiyanv,

Use the below liq code to get the desired result.

(From row In dt1.AsEnumerable
	Group row By key = New With { 
	Key.name = row.Item("Name")} Into grp = Group
	Select dt1Clone.LoadDataRow(New Object() {
		key.name,
		String.Join(",",grp.Select(Function(x)x("Address").ToString).ToArray)
	}, True)).CopyToDataTable

where datClone would be the clone of the actual datatable, to get the same structure as dt1

Thanks,
Sanjit

7

Hi @agathiyanv

You can try this one, it will remove dublicate data according to particular column name

dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)(“ColumnName”)).Select(Function(g) g.First).CopyToDataTable()

Regards

8

It removed the row also, But I need the data.

image
image1109Ă—49 24.3 KB

i need in this format

9

@agathiyanv Use below code

(From row In dt1.AsEnumerable
	Group row By key = New With { 
	Key.name = row.Item("Name")} Into grp = Group
	Select dt1Clone.LoadDataRow(New Object() {
		key.name,
		String.Join(" ",grp.Select(Function(x)x("Address").ToString).ToArray)
	}, True)).CopyToDataTable
10

can you show it with sample workflow?

11

Hey @agathiyanv,

Attaching the workflow for you reference.
Sequence.xaml (9.5 KB)

Thanks,
Sanjit

12

how to remove the commas??

13

Hey @agathiyanv Replace the
String.Join(“,”,grp.Select(Function(x)x(“Address”).ToString).ToArray) with
String.Join(" ",grp.Select(Function(x)x(“Address”).ToString).ToArray)

Thanks,
Sanjit

1 Like
14

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