Excel function that returns column of a pivot table as an array

A

azlefty

New Member
Joined
Sep 11, 2012
Messages
2
Hello Mr. Excel and friends,

Please help. I'm trying unsuccessfully to write a function that looks for a column in a pivot table and returns the entire column as a text string. The function should take a pivot table and a text string as an argument. The text string is the name of one of the columns. The function needs to return the entire column (excluding the header and footer rows) as an array.

In the example I pasted below, the function should take the pivot table (PivotTable1) and a color, say "Red", as arguments, and then return the values in the Red column as a 48x1 single vector array, in the order shown, starting with 43 and ending with 1. I can only post a text version of the spreadsheet; I was not able to post the actual spreadsheet.

Thanks everyone.

[TABLE="width: 405"]
<colgroup><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Average of Cost[/TD]
[TD][/TD]
[TD]Color[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Hour[/TD]
[TD]Blue[/TD]
[TD]Red[/TD]
[TD]Yellow[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2012[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$12.00[/TD]
[TD="align: right"]$43.00[/TD]
[TD="align: right"]$41.00[/TD]
[TD="align: right"]$32.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$36.00[/TD]
[TD="align: right"]$42.00[/TD]
[TD="align: right"]$26.00[/TD]
[TD="align: right"]$34.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$38.00[/TD]
[TD="align: right"]$44.00[/TD]
[TD="align: right"]$29.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$27.00[/TD]
[TD="align: right"]$33.00[/TD]
[TD="align: right"]$22.00[/TD]
[TD="align: right"]$27.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$43.00[/TD]
[TD="align: right"]$4.00[/TD]
[TD="align: right"]$44.00[/TD]
[TD="align: right"]$30.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$35.00[/TD]
[TD="align: right"]$11.00[/TD]
[TD="align: right"]$35.00[/TD]
[TD="align: right"]$27.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]$13.00[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$47.00[/TD]
[TD="align: right"]$22.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]$13.00[/TD]
[TD="align: right"]$12.00[/TD]
[TD="align: right"]$25.00[/TD]
[TD="align: right"]$16.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$9.00[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$4.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$49.00[/TD]
[TD="align: right"]$46.00[/TD]
[TD="align: right"]$25.00[/TD]
[TD="align: right"]$40.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]$41.00[/TD]
[TD="align: right"]$19.00[/TD]
[TD="align: right"]$31.00[/TD]
[TD="align: right"]$30.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]$16.00[/TD]
[TD="align: right"]$45.00[/TD]
[TD="align: right"]$30.00[/TD]
[TD="align: right"]$30.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]$30.00[/TD]
[TD="align: right"]$33.00[/TD]
[TD="align: right"]$38.00[/TD]
[TD="align: right"]$33.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$22.00[/TD]
[TD="align: right"]$49.00[/TD]
[TD="align: right"]$28.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]$22.00[/TD]
[TD="align: right"]$38.00[/TD]
[TD="align: right"]$14.00[/TD]
[TD="align: right"]$24.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]$38.00[/TD]
[TD="align: right"]$23.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$22.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]$38.00[/TD]
[TD="align: right"]$21.00[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$22.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]$48.00[/TD]
[TD="align: right"]$31.00[/TD]
[TD="align: right"]$47.00[/TD]
[TD="align: right"]$42.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$27.00[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"]$13.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$35.00[/TD]
[TD="align: right"]$14.00[/TD]
[TD="align: right"]$17.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]$25.00[/TD]
[TD="align: right"]$29.00[/TD]
[TD="align: right"]$17.00[/TD]
[TD="align: right"]$23.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]$33.00[/TD]
[TD="align: right"]$32.00[/TD]
[TD="align: right"]$47.00[/TD]
[TD="align: right"]$37.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]$30.00[/TD]
[TD="align: right"]$18.00[/TD]
[TD="align: right"]$36.00[/TD]
[TD="align: right"]$28.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]$42.00[/TD]
[TD="align: right"]$25.00[/TD]
[TD="align: right"]$47.00[/TD]
[TD="align: right"]$38.00[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2012[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$47.00[/TD]
[TD="align: right"]$48.00[/TD]
[TD="align: right"]$49.00[/TD]
[TD="align: right"]$48.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$28.00[/TD]
[TD="align: right"]$12.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$29.00[/TD]
[TD="align: right"]$44.00[/TD]
[TD="align: right"]$16.00[/TD]
[TD="align: right"]$29.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$24.00[/TD]
[TD="align: right"]$35.00[/TD]
[TD="align: right"]$24.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$4.00[/TD]
[TD="align: right"]$9.00[/TD]
[TD="align: right"]$30.00[/TD]
[TD="align: right"]$14.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$13.00[/TD]
[TD="align: right"]$31.00[/TD]
[TD="align: right"]$47.00[/TD]
[TD="align: right"]$30.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]$30.00[/TD]
[TD="align: right"]$39.00[/TD]
[TD="align: right"]$39.00[/TD]
[TD="align: right"]$36.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]$29.00[/TD]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$21.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$43.00[/TD]
[TD="align: right"]$28.00[/TD]
[TD="align: right"]$19.00[/TD]
[TD="align: right"]$30.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$22.00[/TD]
[TD="align: right"]$17.00[/TD]
[TD="align: right"]$13.00[/TD]
[TD="align: right"]$17.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]$6.00[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$5.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]$17.00[/TD]
[TD="align: right"]$14.00[/TD]
[TD="align: right"]$40.00[/TD]
[TD="align: right"]$23.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]$17.00[/TD]
[TD="align: right"]$37.00[/TD]
[TD="align: right"]$40.00[/TD]
[TD="align: right"]$31.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]$17.00[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$43.00[/TD]
[TD="align: right"]$23.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]$12.00[/TD]
[TD="align: right"]$34.00[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$16.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]$26.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$31.00[/TD]
[TD="align: right"]$35.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]$34.00[/TD]
[TD="align: right"]$32.00[/TD]
[TD="align: right"]$26.00[/TD]
[TD="align: right"]$30.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$37.00[/TD]
[TD="align: right"]$38.00[/TD]
[TD="align: right"]$25.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]$17.00[/TD]
[TD="align: right"]$17.00[/TD]
[TD="align: right"]$21.00[/TD]
[TD="align: right"]$18.33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$40.00[/TD]
[TD="align: right"]$24.00[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$24.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]$37.00[/TD]
[TD="align: right"]$37.00[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$27.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]$34.00[/TD]
[TD="align: right"]$45.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$26.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]$31.00[/TD]
[TD="align: right"]$16.00[/TD]
[TD="align: right"]$7.00[/TD]
[TD="align: right"]$18.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]$21.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$17.00[/TD]
[TD="align: right"]$13.00[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]$24.48[/TD]
[TD="align: right"]$26.31[/TD]
[TD="align: right"]$26.65[/TD]
[TD="align: right"]$25.81[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Maybe this

Code: 
Function selColorRange(SheetName As String, PvtName As String, PvtField As String, _
                        PvtItem As String) As Range
    
    Set selColorRange = ThisWorkbook.Sheets(SheetName).PivotTables(PvtName).PivotFields(PvtField). _
        PivotItems(PvtItem).DataRange
    
End Function

Assuming
Sheetname = Sheet1
PivotTable name = PVT1
Field Name = Color
Color = Red

Usage (for example to get the MAX of Red values)

=MAX(selColorRange("Sheet1","PVT1","Color","Red"))

M.
 
Upvote 0
Marcelo Branco said:
Maybe this

Code: 
Function selColorRange(SheetName As String, PvtName As String, PvtField As String, _
                        PvtItem As String) As Range
    
    Set selColorRange = ThisWorkbook.Sheets(SheetName).PivotTables(PvtName).PivotFields(PvtField). _
        PivotItems(PvtItem).DataRange
    
End Function

Assuming
Sheetname = Sheet1
PivotTable name = PVT1
Field Name = Color
Color = Red

Usage (for example to get the MAX of Red values)

=MAX(selColorRange("Sheet1","PVT1","Color","Red"))

M.
Click to expand...

Yes, that seemed to work without the MAX function. Thanks Marcelo! Much appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Formula to get the range of certain Category
2 3
Replies
25
Views
795
Peter_SSs
Peter_SSs
T
  • Question Question
copy last column for each sheet and the ID for each adjacent cells
Replies
0
Views
138
tubrak
T
Hrishi
  • Question Question
Dynamic Sumproduct
Replies
5
Views
441
Fluff
Fluff
T
  • Solved
Repeat filling QTY into the empty cells of adjacent cells based on the previous column
Replies
6
Views
310
tubrak
T
J
  • Question Question
Sumproduct with different array sizes
2
Replies
19
Views
740
jkj115
J

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top