Based on two column value we need concatenate remain column value _

column name =“name,name1,name2,name3,” column will store in assign and its dynamic


above input image

Screenshot 2023-12-28 003946
output image

i need code without doing for each looping

Hi @Jijendiran_Murugan

You can use the Linq expression to get the output in excel file.

Take a Read range workbook activity to read the excel and store in a datatable called dtInput.
After Read range workbook, use the Invoke code activity to run the vb code. Give the below code in the Invoke code activity.

Dim updatedDataTable = dtInput.Clone() ' Creates a new DataTable with the same structure
For Each row As DataRow In dtInput.Rows
    Dim newRow As DataRow = updatedDataTable.NewRow()
    newRow.ItemArray = row.ItemArray
    newRow("Name") = String.Join(",", {row("Name"), row("Name1"), row("Name2")})
    updatedDataTable.Rows.Add(newRow)
Next
dtOutput = updatedDataTable
 
dtOutput.Columns.Remove("Name1")
dtOutput.Columns.Remove("Name2")

In arguments create two arguments dtInput and dtOuput as below

After that use write range workbook activity to write the dtOuput to excel.

Check the below image for workflow

Hope it helps!!

Hi @Jijendiran_Murugan

How about the following?

Input:
image

Output:
image

Code:
Sequence7.xaml (9.9 KB)

Cheers!!

Hi @Jijendiran_Murugan

First read the excel into dt and use for each row activity
inside for each row take assign activity and add assign activity row(“name”)=row(“name”).tostring+“,”+row(“name1”).tostring+“,”+row(“name2”).tostring
finally
write the dt in excel

Thanks,
Suresh.

Hi @Jijendiran_Murugan

Input:


Use this Linq query:

out_dt= (From row In dt.AsEnumerable()
                          Let concatenatedNames = String.Join(", ", {row("name").ToString(), row("name1").ToString(), row("name2").ToString()}.Where(Function(name) Not String.IsNullOrWhiteSpace(name)))
                          Select dt.Clone().Rows.Add(row("class"), row("subject"), concatenatedNames)).CopyToDataTable().DefaultView.ToTable(False, "class", "subject", "name")

Output:

Regards

@Jijendiran_Murugan

dt_class.AsEnumerable.Select(function(x) dt_final.Clone.Rows.Add(x(“Class”).ToString,x(“Subject”).ToString,x(“Name”).ToString+“,”+x(“Name1”).ToString+“,”+x(“Name2”).ToString)).CopyToDataTable

Cheers!!

Hi @Jijendiran_Murugan

You can also use Macros Invoke VBA

Forum.zip (220.8 KB)

Hope it helps!!

Hi @Jijendiran_Murugan

  1. Read File using Read Range Activity and assign in DT as DataTable
  2. Take Assign Activity and use below expression

DT= (From row In DT.AsEnumerable()
Let AllNames = String.Join(", ", {row(“name”).ToString(), row(“name1”).ToString(), row(“name2”).ToString()}.Where(Function(name) Not String.IsNullOrWhiteSpace(name)))
Select DT.Clone().Rows.Add(row(“class”), row(“subject”), AllNames)).CopyToDataTable().DefaultView.ToTable(False, “class”, “subject”, “name”)

  1. Write Range

Hope it will helps you :slight_smile:
Cheers!!