Group Pivot Items in new Pivot Field

[illinitom]

I am creating Pivot Table that has counts of different buckets. I am trying to consolidate various field for reporting.

Trying to duplicate record macro routine run into bugs.

Here is a snippet of code

            For Each pvtItem In PVTable.PivotFields("buckets").PivotItems
'                MsgBox pvtItem.name
                pvtItem.Visible = False
            Next




            ' Group into larger bucket
            .PivotSelect "buckets[c190,Q215,s191]", _
                xlDataAndLabel + xlFirstRow, True
            Selection.Group
            .PivotFields("buckets2").PivotItems("Group1").Caption = "1st fails"

            ' 2nd Bucket
            .PivotSelect "buckets[h193,L210]", _
                xlDataAndLabel + xlFirstRow, True
            Selection.Group
            .PivotFields("buckets2").PivotItems("Group1").Caption = "2nd Fails"

I can't seem to find anything in this forum that addresses this. Any thoughts?

 

[RoryA]

Can you be more specific as to what the actual problem is?

 

[illinitom]

I'll try. Below is current pivot table. In VBA, I would like to group bin1 & bin2, bin3 & bin4, bin5-bin7 and apply with a new label. I recorded a macro and got the command:

ActiveSheet.PivotTables("Pivot").PivotSelect "Sort[bin1,bin2]", _
        xlDataAndLabel + xlFirstRow, True

When I try and duplicate that for multiple groupings, I get runtime error 1004: Formula is not complete.

I'm guessing there is a cleaner way to implement this task.

[TABLE="width: 227"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]bin1[/TD]
[TD="align: right"]0.49%[/TD]
[/TR]
[TR]
[TD]bin2[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]bin3[/TD]
[TD="align: right"]0.07%[/TD]
[/TR]
[TR]
[TD]bin4[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]bin5[/TD]
[TD="align: right"]49.19%[/TD]
[/TR]
[TR]
[TD]bin6[/TD]
[TD="align: right"]1.12%[/TD]
[/TR]
[TR]
[TD]bin7[/TD]
[TD="align: right"]0.07%[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

My preference is generally to do the grouping in the source data, but you can use code like this:

    Dim PT                    As Excel.PivotTable
    Dim rg                    As Excel.Range
    Dim pf                    As Excel.PivotField

    Set PT = ActiveSheet.PivotTables("Pivot")
    Set pf = PT.PivotFields("Sort")

    Set rg = Union(pf.PivotItems("bin1").LabelRange, pf.PivotItems("bin2").LabelRange)
    rg.Group
    Set rg = Union(pf.PivotItems("bin3").LabelRange, pf.PivotItems("bin4").LabelRange)
    rg.Group
    Set rg = Union(pf.PivotItems("bin5").LabelRange, pf.PivotItems("bin6").LabelRange, pf.PivotItems("bin7").LabelRange)
    rg.Group
    With PT.PivotFields("Sort2")
        .PivotItems(1).Caption = "1st fails"
        .PivotItems(2).Caption = "2nd fails"
        .PivotItems(3).Caption = "3rd fails"
    End With

 

[illinitom]

Working with the source code is preferable, but often is >100K rows of data

My understanding is Application.transpose only works with 64K fields, and writing line by line is super slow. If there's a way to handle chucks of the source data, that doesn't bog down my system, I'm open to it.

 

[RoryA]

What does Transpose have to do with anything? Where does the source data come from?

 

[illinitom]

What does Transpose have to do with anything? Where does the source data come from?

Source code is pulled from DB that I don't have write permissions to. It is dumped into .csv format, that I'm pulling into excel. I could modify fields with other program (perl, etc), but was hoping to contain all processing in VBA.

 

[RoryA]

Do you just open the csv file in Excel or query it? If you query it, you could add a grouping column to that query.

DO you have Power Query or Power Pivot available?

 

[illinitom]

Do you just open the csv file in Excel or query it? If you query it, you could add a grouping column to that query.

DO you have Power Query or Power Pivot available?

Unfortunately, I'm working with v2007, and I don't see either of those addins supported.

 

[RoryA]

They're not available to you, but what about the first of the three questions? How do you access the csv?