Excel Automation Duplicate Issue

Hi Team,

I have an excel file let say which has columns.
Company Code , Target , Update , Status
if Company code value is same in any row that i need to merge those rows .Additionally in Target column I need all matched rows value with separated by -

ex-
Company Code , Target , Update , Status
1234 , yes , Low , Success
9087 , yes , High, Success
1234 , yes , Medium, Success

Output-
Company Code , Target , Update , Status
1234 , yes , Low- Medium , Success
9087 , yes , High, Success

Hi,

Isn’t the above sample the reverse of the column names Update and Target?
For now, can you try the following sample?

dt.AsEnumerable.GroupBy(Function(r) r("Company Code").ToString).Select(Function(g) dt.Clone.LoaddataRow({g.Key,String.Join("-",g.Select(Function(r) r("Update").ToString).Distinct),String.Join("-",g.Select(Function(r) r("Target").ToString).Distinct),String.Join("-",g.Select(Function(r) r("Status").ToString).Distinct)},False)).CopyToDataTable()

Sample20231204-2a.zip (3.1 KB)

Regards,

Assign Activity
dtResult = dtOrig.Clone

Assign Activity
dtResult =

(From d in dtOrig.AsEnumerable
Group d by k=d("Company Code").toString.Trim into grp=Group
Let tg = String.Join("-", grp.Select(Function (g) g("Target").toString.Trim))
Let gf = grp.First()
Let ra = new Object(){gf("Company Code"),tg,gf("Update"),gf("Status") }
Select dtResult.Rows.Add(ra)).CopyToDataTable

A few adapations maybe are to do

Also have a look here:

@Yoichi can we make the column dynamic instead of providing column name in linq query because in original file I have around 40 Columns.

yes, but we rely on your exact column structures:

(From d in dtOrig.AsEnumerable
Group d by k=d("Company Code").toString.Trim into grp=Group
Let tg = String.Join("-", grp.Select(Function (g) g("Target").toString.Trim))
Let gfia = grp.First().ItemArray
Let ra = gfia.Take(noOfColsBefore).Append(tg).Concat(gfia.Skip(noOfColsAfter)).ToArray
Select dtResult.Rows.Add(ra)).CopyToDataTable

@ppr it says noofColsBefore and noofcolsafter not declared error in assign activity

Yes this is correct, here you have to replace by the number (count) of cols before and after

@ppr it not joining the duplicated row value in Update column with -

please share with us

  • the Datacolumn structures from the input and expected output
  • details of what you had modelled