Excel split based on count

[ranaprathap928 (Rana reddy)]

Hi everyone,

I need some help in Excel split. I have a Excel data and i need to split it like With 10 rows of data into a new sheet. If i have 25 records it will be in 3 sheets each sheet contains 10 and what ever remaining will be in last sheet. But here a catch i have duplicate rows also in the input, if my 10th and 7th row contains same record then i need to make 1st with 9 rows of data from 10th row to 19th will be in another sheet. In short we should not split the duplicates record into multiple sheets it should be in a single sheet.

This is the code which i made with LINQ , it will group the data with each record with duplicates will be a separate group. Now i want to pick 10 rows which contains the addition of groups i need to write in new sheet.

Another approach:

I have created COUNTSHEET using VBA and it’s having unique values and the duplicate count.

Is there any possibility to create new sheet with 10 rows of data using VBA.

Either VBA code input or LINQ query input will be appreciated.

[ppr (Peter Preuss)]

We would recommend to handle the grouping diffferent e.g. each group is a list of Datarows
then we can aggregate and respect chunck size and group member completness

[ranaprathap928 (Rana reddy)]

Hi @ppr could you elaborate and explain me. I got struck there from morning. I made grouping with duplicates each duplicate record have a group, after that I’m unable to split into separate datatables with the count of 10 it may vary but it should not exceed count 10 and it should not split the single record and should not write in 2 datatables.

[ppr (Peter Preuss)]

As far we have understood

Per cheat max 10 rows
But do not split groups

Result with given input would look like this:

[ranaprathap928 (Rana reddy)]

@ppr that’s correct. can you provide me the code what you have used. It will be helpful for me.

[ppr (Peter Preuss)]

Variables:

Preps

Groups =
(From d In ReadDT.AsEnumerable
Group d By k=d(0).toString.Trim Into grp=Group
Select g = grp.ToList()).ToList

outerlist: Groups, innerlist: Groupmembers

Splittings

Then:
tmpSegment= tmpSegment.Concat(grp).ToList
Else:
TableList = TableList.Append(tmpSegment.CopyToDataTable).toList
Last
TableList = TableList.Append(tmpSegment.CopyToDataTable).toList

So resulting List(of DataTable) can be used again within a For each e.g. for writing it to excel

Find starter help:
Main.xaml (12.1 KB)

Kindly note:
when a group has more then 10 rows, it still will become a segement within the tablelist

[ranaprathap928 (Rana reddy)]

Thanks @ppr it’s working perfectly