GROUPBY() and PIVOTBY()

[Paul_Hossler]

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

31935

2. PIVOTBY header = 3 seems to do the same thinkm but there's also a extra entry

31936

What am I doing wrong, or it just buggy?

[Aussiebear]

As I understand Groupby, then this should result in your M3:N10 results. =Groupby(A3:A21, F3:F21,SUM)

[Paul_Hossler]

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 case

31938

[Paul_Hossler]

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/office/groupby-function-5e08ae8c-6800-4b72-b623-c41773611505




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.





31939

[Aussiebear]

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

=GROUPBY(DataTable[[#Headers],[#Data],[AAA]],DataTable[[#Headers],[#Data],[FFF]],SUM,3,1)

[Paul_Hossler]

Thanks -- That's close to what I FINALLY got to work:clap:

Not sure which way is better :think:

31943

[p45cal]

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.

[Paul_Hossler]

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

[Aussiebear]

.... 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.