How to group a datatable and convert table style?

[rockchord]

Hi,

How to convert EXCEL table?

This is the current form

ID	Company	Code	Employee Name	Employee Num	Employee Phone
1	SAAB	P0340	Abigail	A111111	101
2	SAAB	P0340	Beryl	B222222	202
3	Speedway	S6665	Julia	J444444	404
4	Speedway	S6665	Rita	R555555	505
5	netflix	Q3365	Vivien	V666666	606

Output…

ID	Company	Code	Employee Name(1)	Employee Num(1)	Employee Phone(1)	Employee Name(2)	Employee Num(2)	Employee Phone(2)
1	SAAB	P0340	Abigail	A111111	101	Beryl	B222222	202
2	Speedway	S6665	Julia	J444444	404	Rita	R555555	505
3	netflix	Q3365	Vivien	V666666	606

How can I go about doing this please?
Thanks in advance!

[rockchord]

I have found the solution from the previous article
But not sure if this is correct
Please guide me to the correct code,thank you。

(From d In dtData.AsEnumerable
Group d By k1=d(“Company”).toString, k2=d(“Code”).toString.Trim Into grp = Group
Let cn = If(grp.Select(Function (rn) rn(“Employee Name”).toString).toarray,grp.Select(Function (rn) rn(“Employee Name”).toString))
Let cs = If(grp.Select(Function (rn) rn(“Employee Num”).toString).toarray,grp.Select(Function (rn) rn(“Employee Num”).toString))
Let cx = If(grp.Select(Function (rn) rn(“Employee Phone”).toString).toarray,grp.Select(Function (rn) rn(“Employee Phone”).toString))
Let ra = New Object(){k1,k2, cn(0),cs(0),cx(0),cn(1),cs(1),cx(1)}
Select dtReport.Rows.Add(ra)).CopyToDataTable

[Anil_G (Anil Gorthi)]

@rockchord

Please try this in assign activity

dt = dt.AsEnumerable.GroupBy(function(x) x("Company").ToString).Select(function(x) x.First).CopyToDataTable

Cheers

[rockchord]

Sorry, the operation was not successful.

[Anil_G (Anil Gorthi)]

@rockchord

Please tell the issue …not successful will not help you get the answer

Cheers

[sudster]

@Anil_G

Please tell the issue

Extra employees of the same company are added as additional columns…

[Anil_G (Anil Gorthi)]

@sudster

Oww thanks for that I did not hppen to see the full output table…let me fix it

@rockchord

A quick wuestion here if there are 3 same company rows there there should be 3 columns?if so linq might not be a good way

Cheers

[rockchord]

@Anil_G

A quick wuestion here if there are 3 same company rows there there should be 3 columns?if so linq might not be a good way

There is a limit of two

[rockchord]

The running results are as follows

ID	Company	Code	Employee Name	Employee Num	Employee Phone
1	SAAB	P0340	Abigail	A111111	101
2	Speedway	S6665	Julia	J444444	404
3	netflix	Q3365	Vivien	V666666	606

[sudster]

If you really need ID to be in sequence, there’s additional steps required.