How to convert EXCEL table?

[Bosch]

Hi,

How to convert EXCEL table?

This is the current form

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

Output…

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

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

[Yoichi]

Hi,

Can you try the following sample?

dtResult = dt.AsEnumerable.SelectMany(Function(r) Enumerable.Range(1,3).Select(Function(i) dtResult.LoadDataRow({r("ID"),r("Company"),r("Code"),r("Employee Name("+i.toString+")"),r("Employee Num("+i.toString+")"),r("Employee Phone("+i.tostring+")")},False))).Where(Function(r) Not String.IsNullOrEmpty(r("Employee Name").ToString)).CopyToDataTable

Sample20230421-2L.zip (14.8 KB)

Regards,

[rockchord]

Thank you for your prompt reply.

It’s my fault.I misdescribed my doubt.

This is the current form

Company	Code	Employee (1)	Employee (2)	Employee (3)
SAAB	P0340	Abigail/A111111/101	Beryl/B222222/202	Daisy/D333333/303
Speedway	S6665	Julia/J444444/404	Rita/R555555/505
netflix	Q3365	Vivien/V666666/606

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

[Yoichi]

Hi,

How about the following?

dtResult = dt.AsEnumerable.SelectMany(Function(r) Enumerable.Range(1,3).Select(Function(i) dtResult.LoadDataRow({"",r("Company"),r("Code")}.Concat( r("Employee ("+i.toString+")").ToString.Split({"/"c})).ToArray,False))).Where(Function(r) Not String.IsNullOrEmpty(r("Employee Name").ToString)).CopyToDataTable

Sample20230421-2Lv2.zip (21.2 KB)

Regards,

[rockchord]

I can’t thank you enough.
It worked fine.