Call By Month aggregate

[pivotguy]

I would like to aggregate product calls by month. Please see the attached file. Appreciate any help on this. I have excel 2013. But added both version.xls and xlsx files.

[mikerickson]

Have you tried a pivot table?

[pivotguy]

No . I did not. I don't know how to accomplish the task using PIVOT option.

[Paul_Hossler]

I agree with Mike -- Pivot Tables are your friend. Well worth some time spent learning how to use them

I had to add two helper columns and split the double calls on one date into two lines, and do some number formatting


Product aggregate by Month_phh.xlsxCapture.JPG

[pivotguy]

Paul - The original file contains ~100 K records. I attached a subset of that file. You manually split the double calls into multiple lines . If the double calls are split into multiple lines. I could have been done the same using PIVOT table. It is not practically possible to split double calls of ~100 K records into multiple lines manually. Any suggestion.

[SamT]

I know nuttink 'bout formerlas, but would'nt a CountIf Month() = Month()) and InStr(Cell, Cell )Work?

Here is my lame attempt to give you an idea of what I mean

=CountIF(AND(Month(Input!$A:$A)=Month(Output!Output!$1:$1)),NOT(ISERROR(Search(Input!$B:$B,Output!$A:$A))))

[pivotguy]

I downloaded excel file "Product aggregate by Month_phh.xlsx" and type your formula into
"=CountIF(AND(Month(Input!$A:$A)=Month(Output!Output!$1:$1)),NOT(ISERROR(Se arch(Input!$B:$B,Output!$A:$A)))) "
into D2 cell. The formula is thowing an error message. Please check.

[Paul_Hossler]

Paul - The original file contains ~100 K records. I attached a subset of that file. You manually split the double calls into multiple lines . If the double calls are split into multiple lines. I could have been done the same using PIVOT table. It is not practically possible to split double calls of ~100 K records into multiple lines manually. Any suggestion.

Write a macro to split the double entry lines, either once to normalize your data, or as an intermediate step and run the PT off of the reformatted table

If you do that, you would not need formulas in my two helper columns; just use VBA to generate the values

[pivotguy]

Can you help me how to approach for a macro

[Paul_Hossler]

Something like this. Assumes some sheet names and formats, but you can update as required

There are some minor efficiencies that could be made if you find it runs too slow

I added more lines to play with on 'Input'

If you have questions, post a sheet with more data to play with

Option Explicit

Sub SplitDataLines()
    Dim iOut As Long, iIn As Long, iInRowsCount As Long
    Dim wsInput As Worksheet, wsTemp As Worksheet, wsOutput As Worksheet
    
    
    
    'set up
    Set wsInput = Worksheets("Input")
    Set wsTemp = Worksheets("List")
    Set wsOutput = Worksheets("PivotTableSheet")
    
    Application.ScreenUpdating = False
    
    'clean existing List
    Range(wsTemp.Cells(2, 1), wsTemp.Cells(2, 1).End(xlDown)).EntireRow.Delete
    
    'move data
    iOut = 2
    iInRowsCount = wsInput.Cells(wsInput.Rows.Count, 1).End(xlUp).Row
    
    'split lines
    With wsInput
        For iIn = 2 To iInRowsCount
        
            Application.StatusBar = "Processing row number " & Format(iIn, "#,##0")
        
            If InStr(.Cells(iIn, 2).Value, "TNK") > 0 Then
                wsTemp.Cells(iOut, 1).Value = .Cells(iIn, 1).Value
                wsTemp.Cells(iOut, 2).Value = .Cells(iIn, 2).Value
                wsTemp.Cells(iOut, 3).Value = "TNK"
                wsTemp.Cells(iOut, 4).Value = DateSerial(Year(.Cells(iIn, 1).Value), Month(.Cells(iIn, 1).Value), 1)
                iOut = iOut + 1
            End If
            If InStr(.Cells(iIn, 2).Value, "HPS") > 0 Then
                wsTemp.Cells(iOut, 1).Value = .Cells(iIn, 1).Value
                wsTemp.Cells(iOut, 2).Value = .Cells(iIn, 2).Value
                wsTemp.Cells(iOut, 3).Value = "HPS (HN IN PS)"
                wsTemp.Cells(iOut, 4).Value = DateSerial(Year(.Cells(iIn, 1).Value), Month(.Cells(iIn, 1).Value), 1)
                iOut = iOut + 1
            End If
            If InStr(.Cells(iIn, 2).Value, "ABL") > 0 Then
                wsTemp.Cells(iOut, 1).Value = .Cells(iIn, 1).Value
                wsTemp.Cells(iOut, 2).Value = .Cells(iIn, 2).Value
                wsTemp.Cells(iOut, 3).Value = "ABL (CHK))"
                wsTemp.Cells(iOut, 4).Value = DateSerial(Year(.Cells(iIn, 1).Value), Month(.Cells(iIn, 1).Value), 1)
                iOut = iOut + 1
            End If
        Next iIn
    End With
    
    'make a name
    On Error Resume Next
    Names("SingleList").Delete
    On Error GoTo 0
    
    wsTemp.Cells(1, 1).CurrentRegion.Name = "SingleList"
    
    With wsOutput
        .Select
        With .PivotTables(1)
            .PivotCache.MissingItemsLimit = xlMissingItemsNone
            .PivotCache.Refresh
            .RefreshTable
        End With
    End With
    
    Application.ScreenUpdating = True
    Application.StatusBar = False
    MsgBox "All Done"
 

End Sub

[Aflatoon]

Add a new column C to calculate the first of the month:
=A2-DAY(A2)+1
and fill down. Then the formula in B2 on Output is:
=COUNTIFS(Input!C:C,B$1,Input!B:B,"*"&$A2&"*")
and fill down, then copy across to the other month columns.

[SamT]

@ Aflatoon: Excel'ent.

[Paul_Hossler]

Yea, that is pretty clever; took me awhile to follow it, but it is nice

However, it fails on row 10 where TNK should be counted two times

I don't know if the HPS and the ABL values could be on a line multiple times also

My original macro also fails since I didn't realize TNK could be doubled up on a single line, but the revised one seems to handle them


Capture.JPG

Option Explicit
Sub SplitDataLines()
    Dim iOut As Long, iIn As Long, iInRowsCount As Long, iMatch As Long
    Dim wsInput As Worksheet, wsTemp As Worksheet, wsOutput As Worksheet
    
    
    
    'set up
    Set wsInput = Worksheets("Input")
    Set wsTemp = Worksheets("List")
    Set wsOutput = Worksheets("PivotTableSheet")
    
    Application.ScreenUpdating = False
    
    'clean existing List
    Range(wsTemp.Cells(2, 1), wsTemp.Cells(2, 1).End(xlDown)).EntireRow.Delete
    
    'move data
    iOut = 2
    iInRowsCount = wsInput.Cells(wsInput.Rows.Count, 1).End(xlUp).Row
    
    'split lines
    With wsInput
        For iIn = 2 To iInRowsCount
        
            Application.StatusBar = "Processing row number " & Format(iIn, "#,##0")
        
            iMatch = InStr(1, .Cells(iIn, 2).Value, "TNK")
            Do While iMatch > 0
                wsTemp.Cells(iOut, 1).Value = .Cells(iIn, 1).Value
                wsTemp.Cells(iOut, 2).Value = .Cells(iIn, 2).Value
                wsTemp.Cells(iOut, 3).Value = "TNK"
                wsTemp.Cells(iOut, 4).Value = DateSerial(Year(.Cells(iIn, 1).Value), Month(.Cells(iIn, 1).Value), 1)
                iOut = iOut + 1
                iMatch = InStr(iMatch + 3, .Cells(iIn, 2).Value, "TNK")
            Loop
            
            
            
            iMatch = InStr(1, .Cells(iIn, 2).Value, "HPS")
            Do While iMatch > 0
                wsTemp.Cells(iOut, 1).Value = .Cells(iIn, 1).Value
                wsTemp.Cells(iOut, 2).Value = .Cells(iIn, 2).Value
                wsTemp.Cells(iOut, 3).Value = "HPS (HN IN PS)"
                wsTemp.Cells(iOut, 4).Value = DateSerial(Year(.Cells(iIn, 1).Value), Month(.Cells(iIn, 1).Value), 1)
                iOut = iOut + 1
                iMatch = InStr(iMatch + 3, .Cells(iIn, 2).Value, "HPS")
            Loop
            
            iMatch = InStr(1, .Cells(iIn, 2).Value, "ABL")
            Do While iMatch > 0
                wsTemp.Cells(iOut, 1).Value = .Cells(iIn, 1).Value
                wsTemp.Cells(iOut, 2).Value = .Cells(iIn, 2).Value
                wsTemp.Cells(iOut, 3).Value = "ABL (CHK))"
                wsTemp.Cells(iOut, 4).Value = DateSerial(Year(.Cells(iIn, 1).Value), Month(.Cells(iIn, 1).Value), 1)
                iOut = iOut + 1
                iMatch = InStr(iMatch + 3, .Cells(iIn, 2).Value, "ABL")
            Loop
        Next iIn
    End With
    
    'make a name
    On Error Resume Next
    Names("SingleList").Delete
    On Error GoTo 0
    
    wsTemp.Cells(1, 1).CurrentRegion.Name = "SingleList"
    
    With wsOutput
        .Select
        With .PivotTables(1)
            .PivotCache.MissingItemsLimit = xlMissingItemsNone
            .PivotCache.Refresh
            .RefreshTable
        End With
    End With
    
    Application.ScreenUpdating = True
    Application.StatusBar = False
    MsgBox "All Done"
End Sub

[Aflatoon]

Then without helper column:
B2 contains:
=SUMPRODUCT((TEXT(Input!$A$2:$A$16,"mmyyyy")=TEXT(B$1,"mmyyyy"))*((LEN(Inpu t!$B$2:$B$16)-LEN(SUBSTITUTE(Input!$B$2:$B$16,$A2,"")))/LEN($A2)))
fill down and copy across.

[Paul_Hossler]

Then without helper column:
B2 contains:
=SUMPRODUCT((TEXT(Input!$A$2:$A$16,"mmyyyy")=TEXT(B$1,"mmyyyy"))*((LEN(Inpu t!$B$2:$B$16)-LEN(SUBSTITUTE(Input!$B$2:$B$16,$A2,"")))/LEN($A2)))
fill down and copy across.

Yea, that works well and catches the double TNKs

Personally I prefer pivot tables (often mixed with VBA) since I like the flexibility to analyze the data in different ways, but if the OP is doing a fixed format report, possibly WS formulas might be better for their purposes

[Aflatoon]

Horses for courses. If power query is available, I'd probably use that to do the reformatting of the data.

[Paul_Hossler]

Horses for courses.

I had to Google that one.

[Aflatoon]

What can I say - misspent youth going racing with my Nana.