Duplicates into their own Excel file

[Seungwan (손승완)]

Hi guys,

I need to separate each duplicate row based on column into their own excel file based on the query from a database.
Example of the data are:

Invoice 1
Invoice 1
Invoice 1
Invoice 2
Invoice 2
Invoice 3

I want them to be separated like below:

File 1:
Invoice 1
Invoice 2
Invoice 3

File 2:
Invoice 1
Invoice 2

File 3:
Invoice 1

Any idea how?

[Marius_Puscasu]

Hi @Seungwan,

Perhaps you could use a Linq query and group by the invoice number.

Please check Linq query for groupby and sum - #3 by GBK

Best regards,
Marius

[Manish540 (Manish Shettigar)]

For File 1 you can use below group by code, @Seungwan
Dt.AsEnumerable.GroupBy(Function(x) x(“ColumnName”).ToString.Trim).Select(Function(s) s(0)).CopyToDataTable()

[ppr (Peter Preuss)]

@Seungwan
lets assume following input:

and following variables:

first we group the data and find out the longest group:

(From d In dtData.AsEnumerable
Group d By k=d(0).toString.Trim Into grp=Group
Select grp.ToList).toList

Longest Group;

`Groups.Max(Function (x) x.Count)`

Prepare a list of empty Datatables:

(From i In Enumerable.Range(0,MaxGroupCount)
Select tbl = dtData.Clone).toList

Iterate over the groups, iterate over the group members and add the member based on its occurence to the corresponding datatable from the tablelist:

Result:

With iterating over the list of datatables the datatables can be written to Excel as common with a write range

Find starter help here:
SplitGroups_1Col_toGrpCountSegment.xaml (10.1 KB)

[Seungwan (손승완)]

Hi Peter,

Thanks for the long explanation for the solution, it works like a charm!