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


1468914688

[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(Sea rch(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(Sea rch(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


14694






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(Input!$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(Input!$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. :sleuth: :read: :reading:

[Aflatoon]

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