Looking for some advice on retrieving data from Pivot Table

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
244
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am lookingfor advice or even if this possible.

In my model, I use Pivot tables and GetPivotData, no problems. And this works well, when my column headings are all unique values.

However, I am now seeing that as the data records increase, the number of unique column headings is also increasing, which means the data in the cells is effectivcely "thinning out".

If I use something like the Grouping function on the column headings, then the GetPivotData function will not work.

How can I effectively use something like the GetPivotData function but in a Grouped column environment?

Maybe and Index/Match with an approximate match instead of an exact match ????

The actual number of headings is not the problem, it is the thinning out of the data sets which is causing the drama.

I hope somebody has an idea on this one.

Thanks in advance

Jeff.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,118
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
hi, not sure what thinning out means.

I would do the grouping, but collaps the pivot, or get rid of the detailed data field, or use subtotals.
Make sure your getpivotdata formula refers to this grouping field (on subtotals when these are used).
Basically keep the pivot simple. As in single dimensions in columns/rows section. Create more pivots if other combinations are required.
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
244
Office Version
  1. 365
Platform
  1. Windows
Hi GraH,
by thining, i just mean the data is spreading out. For example, yesterdays data has row header 1000, todays row headers are 1000, 1002, 1005, 1006, 1007,
1636701597035.png
and now I have a 1 & 3 popping out to the side, where i need everything to be under the 1000 column heading, by grouping, the 1 & 3 will be where they need to be, but because they are under headings 1006 and 1007, doing a getpivotdata on column heading 1000, willnot include the 1 & 3, like wise doing a getpivotdata on column 1006, will return 1,instead of 30.

how do i do this: Make sure your getpivotdata formula refers to this grouping field (on subtotals when these are used).
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,118
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sorry I can't share trough XL2BB for the moment, so need to share a picture :(.

Depending on how the grouping is done (I cant guess based on the small set you shared), you can do the grouping in the data. As you see the getpivotdata refers to the "group sub-total".
1636704916854.png
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
244
Office Version
  1. 365
Platform
  1. Windows
Thanks for that GraH. It now posses an interest question. The raw data is being drawn from Access because Excel and PQ suffer performamce issues with the amount of data I have.

Because you have grouped the data and then created a Pivot table, I will invesitgate how this can be done in SQL.

I can see it can be done, I just don't know how - yet :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,255
Messages
5,836,271
Members
430,414
Latest member
ayla

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
Top