Summarize data from DataTable

Aspose.Cells Product Family
1

Good Day
I would like to customize a table so that it counts the number of times a person’s name (or ID since it’s unique) appears on the list e.g.

ID Name Dept
111 John 41
111 John 41
113 Sarah 55
113 Sarah 55
201 Peter 41
251 Jack 55
341 Mike 49
341 Mike 49

Dept Name Count
41 John 2
Peter 1
Total: 3

49 Mike 2
Total: 2

55 Sarah 2
Jack 1
Total: 3

The first list is how it is currently displayed but want to format the table so that it looks like the list below it where it sort via dept, count the number of times the users appears on the list on the left and have a total per dept.

Can I do this in a DataTable or is there a Aspose.Cell function I’m not aware of.

2

@Johnane,

Thanks for your query.

If you need to present data in MS Excel formats (e.g XLS, XLSX, SpreadsheetML, etc.), you may implement the task by trying any option from the options given below via Aspose.Cells APIs:

  1. Use Smart Markers’s Grouping Data feature, see the document (especially the description/example under “Grouping Data” sub-topic ):
    Smartly importing and placing data with Smart markers|Documentation

  2. You can use Subtotals MS Excel feature, see the document for your reference: Creating Subtotals|Documentation
    (Note, For 2), you got to first import data into the spreadsheet if the data originally is in the DataTable, see the document on how to import data from DataTable: Import Data into Worksheet|Documentation)

Hope, this helps a bit.