columnrange

[Pilgrim_Paul]

I have a question about the ColumnRange of a pivot table. I was unable to find a single reference to ColumnRange anywhere in the InformIT online version of MrExcel's Excel 2016 Pivot Table Data Crunching, far and away the best pivot table reference in print.

I am struggling to phrase the question so you easily know what I am asking. Here goes:

It appears to me that if one specifies a ColumnRange (to form buckets for categorizing the transaction data), that feature overwhelms any previous specs in the Values area. That is, ALL data is then categorized into those buckets. There is no possibility of having additional leftside or rightside columns summarizing data unrelated to the buckets. "You want buckets? You get buckets (and subtotals)! But that's all you get."

Is my surmise accurate or am I missing some GUI action to get what I want?

If I am correct, it appears to severely limit the usefulness of ColumnRange (which is already hindered by the dynamic nature of the report width due to reduced columns if data is filtered). Ex: One guy sells all 8 different SKUs, but another guy specializes in just 5.

Considering these limitations, we are forced to add a bunch of helper columns to the data source to create and manage our own buckets.

"Say it ain't so, Joe."

 

[GlennUK]

Explain what you want to do. Do not include your usage of ColumnRange in your explanation.

 

[mikerickson]

PivotTable.ColumnRange property (Excel)

Office VBA reference topic

docs.microsoft.com

 

[Pilgrim_Paul]

Thanks, GlennUK

My use of the term ColumnRange was a little sloppy, not quite on the mark. I should have said Columns Area, as when using the Field List to drag fields and construct a pivot table.

Suppose I have a large file of ~5,000 line items from ~1,300 sales orders covering the 8 SKUs we sell. I want to summarize Sales by salesperson (col A), showing the number of distinct orders (col B) and then, in the columns area, their sales volume for each SKU (cols C:J), followed by Total Sales (col K).

I am having difficulty easily configuring the order count in col B.

I hope this scenario helps you help me.

Thanks for your time,
Pilgrim Paul