Data Table to filter/group with multiple columns for a transaction

Help Studio
1

Hello! I have a project where the bot has to process an Excel file with thousands of rows and to add a twist, each row with the same column value (See below sample) should be processed as a single transaction.
I am having a hard time formulating a proper design but any tips or tricks are really appreciated particularly how can I group these rows as a single transaction.

Input
image

Expected Output
image

2

Hi @tep_b

=> Build Data Table

image
image743×399 10.5 KB

Output-> dt_Output

=> Read Range Workbook
image
Output-> dt

=> Use below syntax in Assign:

Assign -> dt_Output = (From row In dt.AsEnumerable()
               Group row By
                   ResellerName = row.Field(Of String)("Reseller Name"),
                   EndUserName = row.Field(Of String)("End-User Name"),
                   MFG = row.Field(Of String)("MFG")
               Into Group
               Let Serials = String.Join(", ", Group.Select(Function(x) x.Field(Of String)("Serial")))
               Select dt_Output.Rows.Add(ResellerName, EndUserName, Serials, MFG, Group.Count())).CopyToDataTable()

=> Write Range Workbook dt_Output.

image
image719×269 7.21 KB

data (3).xlsx (9.7 KB)
Input is Input Sheet and Output is Output Sheet.

image
image900×615 33.4 KB

Sequence41.xaml (10.7 KB)

Hope it helps!!

1 Like
3

This is amazing! Thank you for this. Now, the problem that I’m on is how can I get this implemented in REF but that’s another story :slight_smile:

1 Like
4

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