PDA

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.

SamT
11-01-2015, 04:33 PM
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.

SamT
11-02-2015, 09:14 AM
@ Aflatoon: Excel'ent.

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.