How to move excel value to other sheets base on rows quantity?

Help Activities
1

Dear Experts

I have a request as below:

There are 6 billing document in this sheet. Sometimes, there are 2 or 3 products in one billing documents

If there is only one product in one billing document, I need to move all these rows(including headers) to a new sheet(name row1)

If there are 2 products in one billing document, I need to move all these rows(including headers) to a new sheet(name row2)

If there are 3 products in one billing document, I need to move all these rows(including headers) to a new sheet(name row3)

PS: Usually there is only one product in one billing document in this sheet, but sometimes 2-3 products in one billing cases occur. So I need to consider all scenarios

May I know how to do that?
Sample.XLSX (9.2 KB)

image
:arrow_down:
image
image

image
image

image
image

Thank you for you kind help in advance :smiley:

2

Hi @yangyq10

Can you try below

The image shows a sequence in a workflow that reads a workbook, initializes a counter, groups data by a function, and writes the grouped data to new sheets, incrementing the counter with each iteration. (Captioned by AI)
The image shows a sequence in a workflow that reads a workbook, initializes a counter, groups data by a function, and writes the grouped data to new sheets, incrementing the counter with each iteration. (Captioned by AI)828×837 37.4 KB

DT.AsEnumerable.GroupBy(Function (x) x("Billing Document").ToString.Trim).Select(Function (x) x.CopyToDataTable).ToList

Output:

Sample (8).XLSX (14.3 KB)

Regards,

3

@lrtetala

Thank you for your quick solution

I don’t need to put every single billing document in a new sheet

Only the ones with one product in one billing document in row1 together
and the ones with two products in one billing document in row2 together
and the ones with three products in one billing document in row3 together

Can you help take a look how to adjust the coding to meet the request?

Thank you again :smile:

image

4

in additional to above:

We can have a flow like:

The image shows a workflow sequence in UiPath where a DataTable is converted to a list of DataTables and iterated through, writing each table to a separate workbook with a dynamic name. (Captioned by AI)
The image shows a workflow sequence in UiPath where a DataTable is converted to a list of DataTables and iterated through, writing each table to a separate workbook with a dynamic name. (Captioned by AI)674×684 52.8 KB

with for each details:
grafik

we can seperate the into groups based on Billing Document
then we want to group again based on the group Member account
This we can achieve with a second grouping
From this aggregated groups we return the segment tables

LINQ:

(From d In dtData.AsEnumerable
Group d By k=d("Billing Document").toString.Trim Into grp=Group
Group grp By c=grp.Count Into grp2=Group
Let t = grp2.SelectMany(Function (x) x).CopyToDataTable
Order By grp2.First().Count
Select tbl=t).ToList

Samples:
grafik

UPD1 - fixed the Ordering

1 Like
5

@ppr

Thank you for the additional guidance :smiley:

I have one more small question

After split, if I want to copy all rows in each billing document and paste to another template for next move, what should add in this process?

For example:

For HB00012348, I need to copy row 2-4 to another template and save it with the billing document name(HB00012348)

For HB00012349, I need to copy row 5-7 to another template and save it with the billing document name(HB00012349)

image

6

You should decide and fix the requirement

  • splitting all different Billing Docs into different tables

OR

  • grouping all Billing Document with the same group Member Count into a table
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.