davidblues
New Member
- Joined
- Jan 8, 2020
- Messages
- 2
- Office Version
- 2016
- Platform
- Windows
I'm an experienced user of VBA in Excel, bur pretty new to pivot tables. I've been catching up lately, and I'm even gonna have to use Power Query extensively, but so far there's this thing I can't figure out how to solve. I've been researching, but I either I can't hit the right search string or the topic really hasn't been addressed. Maybe because it's too simple or straight impossible to do, but in any case, I haven't found anything related.
I have a big and complex set of data (over 400000 rows after unpivoting the original range) that needs to be summarised in a pivot table. There are some nested fields, but some values in those fields have different levels of further nesting. It's difficult to express with words, so I produced a simple reduced example, which can be seen in this picture:
In the example, the idea is to replicate the simple plain table in the upper right corner, but with a pivot table. There are only two colours for chairs, black and grey, and three for tables, black, brown and white. But for the tables there's an extra field regarding its shape, which can be square or round. When I build a pivot table to view the total sales per category, I have a couple of empty rows under the black and grey labels for chairs, as can be seen in table 1. Those rows have to go for the table to be ready for publishing, but I can't figure out how. If you collapse the field "Colour", the collapse happens for all the values of the parent field, as you can see in table 2, so I can't use that. I could stack two tables as in table 3, but that would only work in compact layout, and I can't get rid of the headers in the second table, so that I could get the effect of having only one. Besides, as soon as I modified something in the first table in the stack that implied adding some rows, Excel wouldn't let me do it because it would invade the second table in the stack. The goal would be to have one single pivot table with the looks of the stack in table 3. Any ideas? Is it even possible?
Thanks in advance,
David
I have a big and complex set of data (over 400000 rows after unpivoting the original range) that needs to be summarised in a pivot table. There are some nested fields, but some values in those fields have different levels of further nesting. It's difficult to express with words, so I produced a simple reduced example, which can be seen in this picture:
In the example, the idea is to replicate the simple plain table in the upper right corner, but with a pivot table. There are only two colours for chairs, black and grey, and three for tables, black, brown and white. But for the tables there's an extra field regarding its shape, which can be square or round. When I build a pivot table to view the total sales per category, I have a couple of empty rows under the black and grey labels for chairs, as can be seen in table 1. Those rows have to go for the table to be ready for publishing, but I can't figure out how. If you collapse the field "Colour", the collapse happens for all the values of the parent field, as you can see in table 2, so I can't use that. I could stack two tables as in table 3, but that would only work in compact layout, and I can't get rid of the headers in the second table, so that I could get the effect of having only one. Besides, as soon as I modified something in the first table in the stack that implied adding some rows, Excel wouldn't let me do it because it would invade the second table in the stack. The goal would be to have one single pivot table with the looks of the stack in table 3. Any ideas? Is it even possible?
Thanks in advance,
David
