Hi all, I know this goes against what pivot tables are meant to do, but for display purposes this is what I am trying to achieve:
Data: ................................................. "Standard" Pivot layout:
Vendor ................. Product ...................Vendor ............... Product
ABC LTD ............... Apples ................... ABC LTD ...............Apples
ABC LTD .............. Oranges ............... ..............................Oranges
ABC LTD ............... Bananas ............... .............................Bananas
DEF LTD ...............Apples .....................DEF LTD................Apples
DEF LTD ...............Oranges ............... ...............................Oranges
DEF LTD ...............Bananas ............... ...............................Bananas
DESIRED Pivot layout (new style compact pivot):
Vendor
..ABC LTD
..DEF LTD
..Product
....Apples
....Oranges
....Bananas
The point is not to outline all the different combinations, but just list the various unique vendors, followed by unique products.
The data I am working with is quite a bit more complex (about 20 different fields in the pivot) but the same concept applies, I want to only display UNIQUE values in relation to a higher level field, instead of different combinations of values. My pivot is structured to show people, ID(s), address(es), phone(s), email(s), etc, and I only want to show each unique email a single time, instead of unique combinations of ID/Address/Phone/email.
The data is quite complex, so in some instances there will legitimately be multiple different values, can't really rely on sequential order, etc. I also have a feeling it will be extremely difficult to sort this in my source data, as you have to interpret the "context" in any calculations.
Also if at all possible, I want to keep this in a relatively simple single pivot, I have a feeling if I try and sort the entire thing via VBA or repivoting/lookups, it'll just become unworkably complex due to the large number of fields that need to be manipulated. I also want to keep it in a pivot so that the end user has all the pivot functionality, expand, filter, drill down, etc.
I know it's asking a lot, but any thoughts? And thanks very much!
Data: ................................................. "Standard" Pivot layout:
Vendor ................. Product ...................Vendor ............... Product
ABC LTD ............... Apples ................... ABC LTD ...............Apples
ABC LTD .............. Oranges ............... ..............................Oranges
ABC LTD ............... Bananas ............... .............................Bananas
DEF LTD ...............Apples .....................DEF LTD................Apples
DEF LTD ...............Oranges ............... ...............................Oranges
DEF LTD ...............Bananas ............... ...............................Bananas
DESIRED Pivot layout (new style compact pivot):
Vendor
..ABC LTD
..DEF LTD
..Product
....Apples
....Oranges
....Bananas
The point is not to outline all the different combinations, but just list the various unique vendors, followed by unique products.
The data I am working with is quite a bit more complex (about 20 different fields in the pivot) but the same concept applies, I want to only display UNIQUE values in relation to a higher level field, instead of different combinations of values. My pivot is structured to show people, ID(s), address(es), phone(s), email(s), etc, and I only want to show each unique email a single time, instead of unique combinations of ID/Address/Phone/email.
The data is quite complex, so in some instances there will legitimately be multiple different values, can't really rely on sequential order, etc. I also have a feeling it will be extremely difficult to sort this in my source data, as you have to interpret the "context" in any calculations.
Also if at all possible, I want to keep this in a relatively simple single pivot, I have a feeling if I try and sort the entire thing via VBA or repivoting/lookups, it'll just become unworkably complex due to the large number of fields that need to be manipulated. I also want to keep it in a pivot so that the end user has all the pivot functionality, expand, filter, drill down, etc.
I know it's asking a lot, but any thoughts? And thanks very much!
Last edited:
