Grouping in excel

Help Studio
, , , ,
1

I need to do grouping in excel and once any group found need to insert row and do sum at Quantity column.
This is my input excel.

image

Here 40791345621 and 40799885412 are duplicate or repetitive in so I will insert new row like below
image
and 32 and 18 are the sum of those Quantity.

Any expert please help, how to do this.
Sample.xlsx (9.6 KB)

2

@c3f1e68294fdcf4a1f0a817ca,

It would time taking task to help with a sample workflow so sharing logic only. I hope you would be able to code it. Do let me know if you stuck at any step.

  1. Open excel file using Excel Process Scope
  2. Sort the file with Item No column
  3. Use For Each Excel Row activity to iterate through all the rows
  4. Use a variable to store Item number
  5. If previous row item no = current row Sum it and add to a variable
  6. If previous row item no not matching, add a row before current row and add sum variable.

That’s it.

Thanks,
Ashok :slight_smile:

3

Hi @c3f1e68294fdcf4a1f0a817ca

=> Use Read Range Workbook to read the excel and store the output in a data table dtInput.
=> Use the below code in Invoke Code:

dtOutput = New DataTable
dtOutput.Columns.Add("Item No", GetType(String))
dtOutput.Columns.Add("Quantity", GetType(Integer))

' Group data by "Item No" and calculate sums
Dim groupedData = From row In dtInput.AsEnumerable()
                  Group row By ItemNo = row("Item No").ToString() Into Group
                  Select New With {
                      .ItemNo = ItemNo,
                      .Quantities = Group.Select(Function(r) Convert.ToInt32(r("Quantity"))),
                      .SumQuantity = Group.Sum(Function(r) Convert.ToInt32(r("Quantity")))
                  }

' Iterate through grouped data and construct the output DataTable
For Each Group In groupedData
    Dim firstRow As Boolean = True
    For Each quantity In Group.Quantities
        Dim newRow As DataRow = dtOutput.NewRow()
        If firstRow Then
            newRow("Item No") = Group.ItemNo
            firstRow = False
        End If
		newRow("Item No") = Group.ItemNo
        newRow("Quantity") = quantity
        dtOutput.Rows.Add(newRow)
    Next
    ' Add the sum row with empty "Item No" cell
	If Group.Quantities.Count() > 1 Then
    Dim sumRow As DataRow = dtOutput.NewRow()
    sumRow("Item No") = String.Empty
    sumRow("Quantity") = Group.SumQuantity
    dtOutput.Rows.Add(sumRow)
End If
Next

Invoke Code Arguments:

image
image882×257 9.85 KB

=> Use Write Range Workbook to write dtOutput back to excel in new sheet.

INPUT:

image
image299×523 5.36 KB

OUTPUT:

image
image282×508 5.87 KB

FLOW:

image
image899×580 31.3 KB

XAML:
Sequence1.xaml (9.2 KB)

Regards

4

Thanks @vrdabberu . It working however how to add/insert row before grouping
image

5

Hi @c3f1e68294fdcf4a1f0a817ca

In what conditions do you want insert an empty row.

Regards

6

if any grouping found, then just above that and below that group ( which we are doing). Everything is working fine, just need to add one empty row above the group if any grouping found

7

Hi @c3f1e68294fdcf4a1f0a817ca

Please try the below code in Invoke Code:

' Create the output DataTable with columns
dtOutput = New DataTable
dtOutput.Columns.Add("Item No", GetType(String))
dtOutput.Columns.Add("Quantity", GetType(Integer))

' Group data by "Item No" and calculate sums
Dim groupedData = From row In dtInput.AsEnumerable()
                  Group row By ItemNo = row("Item No").ToString() Into Group
                  Select New With {
                      .ItemNo = ItemNo,
                      .Quantities = Group.Select(Function(r) Convert.ToInt32(r("Quantity"))),
                      .SumQuantity = Group.Sum(Function(r) Convert.ToInt32(r("Quantity")))
                  }

' Iterate through grouped data and construct the output DataTable
For Each Group In groupedData
    ' Add empty row before the group
	 If Group.Quantities.Count() > 1 Then
    Dim emptyRowBefore As DataRow = dtOutput.NewRow()
    emptyRowBefore("Item No") = String.Empty
    emptyRowBefore("Quantity") = DBNull.Value
    dtOutput.Rows.Add(emptyRowBefore)
End If

    Dim firstRow As Boolean = True
    For Each quantity In Group.Quantities
        Dim newRow As DataRow = dtOutput.NewRow()
        If firstRow Then
            newRow("Item No") = Group.ItemNo
            firstRow = False
        End If
        newRow("Item No") = Group.ItemNo
        newRow("Quantity") = quantity
        dtOutput.Rows.Add(newRow)
    Next
    
    ' Add the sum row with empty "Item No" cell
    If Group.Quantities.Count() > 1 Then
        Dim sumRow As DataRow = dtOutput.NewRow()
        sumRow("Item No") = String.Empty
        sumRow("Quantity") = Group.SumQuantity
        dtOutput.Rows.Add(sumRow)
    End If
Next

Regards

1 Like
8

Hi @c3f1e68294fdcf4a1f0a817ca ,

You may try one more way like the below with LINQ query.

Code:

(From row In dtInput.AsEnumerable
Group row By ItemNumber = row("Item No") Into grp =Group
Let Sum = grp.AsEnumerable.sum(Function(grpRow) CInt(grpRow("Quantity").tostring))
Let UniqueRows = grp.Count
Let dtAddedNewRow = grp.Concat({dtOutput.NewRow}).CopyToDataTable
From grpItem In dtAddedNewRow
Select dtOutput.Rows.Add( 
	If( dtAddedNewRow.Rows.IndexOf(grpItem) < UniqueRows,
		grpItem.itemArray,
			If(grp.count>1,
				{"",Sum.tostring},
				{""} )))
).copyToDataTable

Post here for your any issue, in case if you are trying.

Attached xaml flow.
GroupRowAndDoSum.xaml (7.2 KB)

Thanks,
Sagar

9

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