Excel Segregation help with format

[Learner007 (Uipath Learner)]

Hi All, i have a scenario that where i want to segregate excel based on rows count including duplicates and i should not split my same record into 2 excel new sheets.

Attaching the code below

Excel Segrigation.zip (16.7 KB)

Input : If my input comes like this
FIRST NAME
Stacy
Stacy
Stacy
Tan
Tan
Stan
Stan
Stan
Vick
Vick
Vick
Zin

Output should be : 2 excel files each contains 6 rows

1st File :
FIRST NAME
Stacy
Stacy
Stacy
Stan
Stan
Stan

2nd File :

FIRST NAME
Vick
Vick
Vick
Zin
Tan
Tan

Note : Output should not come like this :

FIRST NAME
Stacy
Stacy
Stacy
Tan
Tan
Stan

Stan
Stan
Vick
Vick
Vick
Zin

Stan have 3 records so it should go for single excel. It should not split into 2 excel files. If there are no records to combine 6 at the time we can but in scenario we should not split like same record into 2 files half in 1 new excel and another half in another new excel.

[Emre_Ayyildiz]

Hello,

You can find the code below.
Basically you can create a dictionary to keep names and their counts. And with for each add names to a list of first table names until sum of current counts of names passes the half of total sum. Finally you can filter your datatable by the list that you created to hold the names that belonges first table.
BlankProcess18.zip (642.3 KB)

Regards,
Emre

[Learner007 (Uipath Learner)]

Hi @Emre_Ayyildiz If i keep multiple rows of data it is not working. My input rows are not constant, it will change every time.
Can you please check below file once. In ** Output Sheet** i have mentioned my requirements.

challenge.xlsx (18.7 KB)

[Yoichi]

Hi,

It’s like matter of Combinatorial optimization such as Knapsack problem or Bin packing problem.

Combinatorial optimization

In general, more detailed condition is necessary to write logic. For example, what if the combination does not result in 6 records? etc. (Case of 5 records, 3records, 2records and 2 records)

Regards,

[Learner007 (Uipath Learner)]

Hi @Yoichi thanks for the reply. If the combination doesn’t match then we can split any record and make it 6 then create a new excel.

[Learner007 (Uipath Learner)]

@Yoichi if in final we don’t have 6 means in final we left with only 2 or 3 records them we make it as new excel.

[Learner007 (Uipath Learner)]

Hi @Yoichi this code is working but it’s not giving as accurate to make its grouping only some records and it’s skipping few. If you get time look this once

ReadDt.AsEnumerable().GroupBy(Function(row) row.Field(Of String)(0)).SelectMany(Function(Group) If(Group.Count() < 6, ReadDt.AsEnumerable().Where(Function(row) row.Field(Of String)(0) <> Group.Key AndAlso ReadDt.AsEnumerable().Count(Function(r) r.Field(Of String)(0) = row.Field(Of String)(0)) + Group.Count() = 6).ToList(), New List(Of DataRow)())).Select(Function(value, index) New With {value, index}).GroupBy(Function(x) x.index \ 6).ToDictionary(Function(Group) $“Group{Group.Key + 1}”, Function(Group) Group.Select(Function(x) x.value).CopyToDataTable())

[Yoichi]

Hi,

How about the following sample?

Excel Segrigation_v2.zip (17.4 KB)

This uses the following Combination library.

GitHub - eoincampbell/combinatorics: Combinatorics Library for Microsoft .NET.

It’s necessary to add nuget feed in MangePacakge as the following.

https://api.nuget.org/v3/index.json

Regards,

[Learner007 (Uipath Learner)]

Hi @Yoichi thanks for the code but I’m getting validation error. I have the packages

[Yoichi]

Hi,

For now, can you check if you can access nuget.org via ManagePackage as the following?

Regards,

[Learner007 (Uipath Learner)]

Hi @Yoichi
Yes I can access

[Yoichi]

In my environment, I can find it. Can you try to search by combinatorics as the following?

[Learner007 (Uipath Learner)]

@Yoichi I hav but it’s in error
How can I resolve this error

[Yoichi]

Hi,

Can you try to uninstall it, then remove the following folder, next reinstall the package again?

C:\Users\[username]\.nuget\packages\combinatorics

[Learner007 (Uipath Learner)]

Thanks @Yoichi it’s working perfectly. Thanks for your efforts and helping me with code.