Printable View
Trying to use GROUPBY() and PIVOTBY() but the options don't seem to work as expected, especially the Header Level (0 - 3) one
1. GROUPBY header = 3 I'd expect the GREEN in M:N but I get the one in J:K where it seems to treat the first row of data as a header
Attachment 31935
2. PIVOTBY header = 3 seems to do the same thinkm but there's also a extra entry
Attachment 31936
What am I doing wrong, or it just buggy?
As I understand Groupby, then this should result in your M3:N10 results. =Groupby(A3:A21, F3:F21,SUM)
That does not appear to be the caseQuote:
Originally Posted by Aussiebear
Attachment 31938
More experimenting, but no luck getting the headers
With headers = 3, GROUPBY still 'seems' to read the first data line as the header line
https://support.microsoft.com/en-us/...3-c41773611505
Attachment 31939Quote:
ield_headers A number that specifies whether the row_fields and values have headers and whether field headers should be returned in the results. The possible values are:
Missing: Automatic (default)
0: No
1: Yes and don't show
2: No but generate
3: Yes and show
Note: Automatic assumes the data contains headers based on the values argument. If the 1st value is text and the 2nd value is a number, then the data is assumed to have headers. Fields headers are shown if there are multiple row or column group levels.
Sorry edited post. To return the headers and the values I've selected the headers in the initial grouping so the formula now looks like this
Code:=GROUPBY(DataTable[[#Headers],[#Data],[AAA]],DataTable[[#Headers],[#Data],[FFF]],SUM,3,1)
Thanks -- That's close to what I FINALLY got to work:clap:
Not sure which way is better :think:
Attachment 31943
So it looks like if you want to have 3 as the 4th argument you've got to include the headers in the first 2 arguments, and if you want to use anything else in the 4th argument you don't include headers in the first 2 arguments.
Not intuitive, nor friendly.
It is certainly open to interpetation::think:
The '3 = Yes and show' option was all I was looking to do
ield_headers A number that specifies whether the row_fields and values have headers and whether field headers should be returned in the results. The possible values are:
Missing: Automatic (default)
0: No
1: Yes and don't show
2: No but generate
3: Yes and show
Note: Automatic assumes the data contains headers based on the values argument. If the 1st value is text and the 2nd value is a number, then the data is assumed to have headers. Fields headers are shown if there are multiple row or column group levels.
The 'row_fields' paramater passed to GROUPBY() should be [#all][#AAA]] so that the passed parameter does have headers so that a '3' will show them
I just made the assumption that Excel knew the table had headers so the row_fields parameter just needed the row field
But you have to read Microsoft's mind just right to figure it out, and I didn't
There's many thousands of them and only one of you Paul.Quote:
Originally Posted by Paul_Hossler