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
Capture.JPG
2. PIVOTBY header = 3 seems to do the same thinkm but there's also a extra entry
Capture1.JPG
What am I doing wrong, or it just buggy?
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
As I understand Groupby, then this should result in your M3:N10 results. =Groupby(A3:A21, F3:F21,SUM)
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
That does not appear to be the caseOriginally Posted by Aussiebear
Capture2.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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
Capture.JPG
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.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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)
Last edited by Aussiebear; 04-17-2025 at 01:55 PM.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Thanks -- That's close to what I FINALLY got to work
Not sure which way is better
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
It is certainly open to interpetation:
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
There's many thousands of them and only one of you Paul.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Posting Permissions
Reply With Quote
