View Full Version : Call By Month aggregate
pivotguy
11-01-2015, 10:02 AM
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
11-01-2015, 11:54 AM
Have you tried a pivot table?
pivotguy
11-01-2015, 12:21 PM
No . I did not. I don't know how to accomplish the task using PIVOT option.
Paul_Hossler
11-01-2015, 01:14 PM
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
11-01-2015, 01:43 PM
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.
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
11-01-2015, 06:09 PM
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
11-01-2015, 06:14 PM
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
11-01-2015, 06:25 PM
Can you help me how to approach for a macro
Paul_Hossler
11-01-2015, 07:06 PM
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
11-02-2015, 02:05 AM
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.
Paul_Hossler
11-02-2015, 11:17 AM
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
11-03-2015, 01:22 AM
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
11-03-2015, 07:20 AM
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
11-03-2015, 07:30 AM
Horses for courses. If power query is available, I'd probably use that to do the reformatting of the data.
Paul_Hossler
11-03-2015, 08:23 AM
Horses for courses.
I had to Google that one. :sleuth: :read: :reading:
Aflatoon
11-03-2015, 08:30 AM
:) What can I say - misspent youth going racing with my Nana.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.