Excel Automation Duplicate Issue

[Luffy]

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

[Yoichi]

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,

[ppr (Peter Preuss)]

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:

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode th…

[Luffy]

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

[ppr (Peter Preuss)]

@Luffy

can we make the column dynamic instead of providing column name in linq

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

[Luffy]

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

[ppr (Peter Preuss)]

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

[HowTo] LINQ - First Start # Skip | Take | SkipWhile | TakeWhile Operator

This HowTo gives an introductory overview of the partition Operators: Skip, Take, SkipWhile, TakeWhile Introduction The partition operators are used to fetch a particular subset from a set of items. The returned subset is formed by the contiguous items that are matching the provided condition. Skip / Take Operator The Skip Operator will omit the subsequent items from the given start for a given length and will return the remaining items The Take Operator will return the subsequent items fr…

[Luffy]

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

[ppr (Peter Preuss)]

please share with us

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