How to present my data?

[sobeitjedi]

Hi.

I have a single, simple Excel sheet but I'm not sure how to present the data. To simplify, the data looks like ...

CostCentre | TransactionDate | UserName | Cost | Cost Centre Owner
abc1 | 01/01/18 | John.Smith| 0.72 | Jim.Jones
abc2 | 02/01/18 | John.Smith | 0.13 | Cat.Smith
abc3 | 02/01/18 | Bella.Williams | 0.26 | Jim.Jones

This month, there's over 27,000 rows in this sheet

What I'd like to do ultimately, is email each cost centre owner (there's about 70 cost centre owners, each owner can have one or more code), telling them who has charged to their cost centre(s) this month.

How can I achieve this?

 

[SpillerBD]

Add a helper column to duplicate the Cost Center Name. Limit the character length of this field to 12.
Build a Pivot Table.
Drop the Helper column into the FILTER field of the Pivot Table.
In the Rows drop all the non-value labels.
Drop cost into the Values area, and ensure your desired formatting and calculation.

On the Pivot Table Ribbon/ Analyze, click the drop down for Pivot Table. Click the Drop Down for Options, and then click Show Report Filter Pages...
Now, select all those new sheets. Select all Cells, Copy, Paste Special Values. (There are other ways to disconnect the data but this is most assured.)
You can accomplish editing of the header rows and columns for all sheets while they are selected as a group.

Use the following macro to copy all sheets to a desired folder, the new files being named as the SheetName and ready to send.

'SpltSheets
Sub SplitSelectedWorkSheets()
    Dim ws As Worksheet
    Dim DisplayStatusBar As Boolean
    Dim DestinationPath As Variant
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = ThisWorkbook.Path & "\"
        .Title = "Select a destination folder or create a new destination."
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancelled"
            Exit Sub
        Else
            'MsgBox .SelectedItems(1)
            DestinationPath = .SelectedItems(1)
        End If
    End With
    
    DisplayStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = ActiveWindow.SelectedSheets.Count & " Remaining Sheets"
    
    For Each ws In ActiveWindow.SelectedSheets
        Dim NewFileName As String
       
        'Macro-Enabled
        'NewFileName = ThisWorkbook.Path & "\" & ws.Name & ".xlsm"
        'Not Macro-Enabled
            NewFileName = DestinationPath & "\" & ws.Name & ".xlsx"
            ws.Copy
            'ActiveWorkbook.Sheets(1).Name = "Sheet1"
            'ActiveWorkbook.SaveAs Filename:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
            ActiveWorkbook.SaveAs fileName:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close SaveChanges:=False

    Next
    
    Application.DisplayAlerts = True
    Application.StatusBar = False
    Application.DisplayStatusBar = DisplayStatusBar
    Application.ScreenUpdating = True
    Close 'close all files and folders?
End Sub

 

[sobeitjedi]

Add a helper column to duplicate the Cost Center Name. Limit the character length of this field to 12.
Build a Pivot Table.
Drop the Helper column into the FILTER field of the Pivot Table.
In the Rows drop all the non-value labels.
Drop cost into the Values area, and ensure your desired formatting and calculation.

On the Pivot Table Ribbon/ Analyze, click the drop down for Pivot Table. Click the Drop Down for Options, and then click Show Report Filter Pages...
Now, select all those new sheets. Select all Cells, Copy, Paste Special Values. (There are other ways to disconnect the data but this is most assured.)
You can accomplish editing of the header rows and columns for all sheets while they are selected as a group.

Use the following macro to copy all sheets to a desired folder, the new files being named as the SheetName and ready to send.

'SpltSheets
Sub SplitSelectedWorkSheets()
    Dim ws As Worksheet
    Dim DisplayStatusBar As Boolean
    Dim DestinationPath As Variant
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = ThisWorkbook.Path & "\"
        .Title = "Select a destination folder or create a new destination."
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancelled"
            Exit Sub
        Else
            'MsgBox .SelectedItems(1)
            DestinationPath = .SelectedItems(1)
        End If
    End With
    
    DisplayStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = ActiveWindow.SelectedSheets.Count & " Remaining Sheets"
    
    For Each ws In ActiveWindow.SelectedSheets
        Dim NewFileName As String
       
        'Macro-Enabled
        'NewFileName = ThisWorkbook.Path & "\" & ws.Name & ".xlsm"
        'Not Macro-Enabled
            NewFileName = DestinationPath & "\" & ws.Name & ".xlsx"
            ws.Copy
            'ActiveWorkbook.Sheets(1).Name = "Sheet1"
            'ActiveWorkbook.SaveAs Filename:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
            ActiveWorkbook.SaveAs fileName:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close SaveChanges:=False

    Next
    
    Application.DisplayAlerts = True
    Application.StatusBar = False
    Application.DisplayStatusBar = DisplayStatusBar
    Application.ScreenUpdating = True
    Close 'close all files and folders?
End Sub

Many thanks for the quick and detailed reply. I'm still working on your solution and getting my head around how it works and tweaking it, but just wanted to say a massive thanks and not look like I wasn't grateful for the response.