Page 1 of 1

[Solved] Pivot Table Redundancy

PostPosted: Wed Nov 13, 2019 6:18 pm
by AlexandraNyx
Question -

I make dozens of pivot tables, with the same column values, dozens of them too. All have different row fields.

Is there a way to make these pivot tables with the same column values without manually entering them in each time? Then enter the different row fields.

I hope so. Help!

Re: Pivot Table Redundancy -

PostPosted: Mon Nov 18, 2019 7:43 pm
by Alex1
Can you upload an example of the table, with sensitive data removed, so we can try to find an approach?

Re: Pivot Table Redundancy -

PostPosted: Mon Nov 18, 2019 10:02 pm
by AlexandraNyx
Thanks for the attention Alex. It's a hypothetical table so any kind of table would apply. The column fields are all the same but with different row fields.

Instead of having to create new tables with the same column fields each time with different row fields is the another way to do it?
Thanks

Re: Pivot Table Redundancy -

PostPosted: Mon Nov 18, 2019 11:06 pm
by Alex1
Can you give the criteria you use to select the rows?

Re: Pivot Table Redundancy -

PostPosted: Tue Nov 19, 2019 5:01 pm
by AlexandraNyx
Hi Alex
It'd be hypothetical.
In each new table, row fields would be different, with the same column fields.
I need different tables. Is there a more efficient way to create them than manually filling in the same columns each time.
Thanks partner

Re: Pivot Table Redundancy -

PostPosted: Tue Nov 26, 2019 6:23 pm
by AlexandraNyx
to quote Def Leppard -
"Is anyone out there, does anyone care???"
I just gotta know!

Just foolin

Re: Pivot Table Redundancy -

PostPosted: Tue Nov 26, 2019 7:59 pm
by Villeroy
Without seeing what you're doing, I don't understand the problem. Its always better to attach a sample file. Pivot tables can be complex.

Re: Pivot Table Redundancy -

PostPosted: Wed Nov 27, 2019 1:55 am
by AlexandraNyx
Hurray!

Someone is there.

Take any pivot table using 10 column values.

The question is. is there a way to make a second or a third pivot table, etc, using the SAME column values without manually putting the SAME values in each time?

The row field would be different in each different pivot table.

Re: Pivot Table Redundancy -

PostPosted: Wed Nov 27, 2019 1:57 pm
by Villeroy
Lets take this pivot from another topic. The pivot on Sheet2 has values 2-9 in column field "TIER". The fields are derived from the source data. Every pivot table derived from the same source table with column field "TIER" will have the same column field values 2-9.

Re: Pivot Table Redundancy -

PostPosted: Wed Nov 27, 2019 5:49 pm
by Villeroy
If you want to filter the column field (for instance show or hide items 2,4,6,8) and sync this filter with other pivots, this is not possible without writing macro code.
You can merge pivot tables into one by adding more row fields and calculated data fields.
You can use a database and then build your pivots from the same filtering query. This might be the most elegant solution if my assumption about column field filtering is correct.

Re: Pivot Table Redundancy -

PostPosted: Wed Nov 27, 2019 11:25 pm
by AlexandraNyx
thank you my friend
you always come through
alexandra
let me absorb this prior to adding solved

Re: Pivot Table Redundancy -

PostPosted: Thu Nov 28, 2019 12:00 pm
by Villeroy
Is my assumption true that you want to apply the same filter on column fields by means of the drop-down button besides the column field name?

Re: Pivot Table Redundancy -

PostPosted: Thu Nov 28, 2019 3:48 pm
by AlexandraNyx
I would like to understand how that works. Thanks. Specifically now I'm using the same columns in the data field. Like Column A through Column Z. In each pivot table, the Row fields would be different (ABC) or (ADF) or (EXZ) etc. What you're explaining could be a solution to something I hadn't considered. Thanks. Always open to learning new applications.
alexandra

Re: Pivot Table Redundancy -

PostPosted: Sat Nov 30, 2019 10:31 am
by Villeroy
Please post a sample of what you have.

Re: Pivot Table Redundancy -

PostPosted: Sat Nov 30, 2019 11:46 am
by Villeroy
Download the attached files to your home directory.
Please call menu:Tools>Options>Base>Databases and register the downloaded database document as "PivotDatabase". This is required for the spreadsheet to use this database as a data source.
The attached database has a 3-column table "Data" with "Name" for the row fields, "Category" (letters A-Z) for the column fields and "Value" for the data fields.
A second table "Categories" stores the category letters you want to see in the pivots.
A query "Query1" shows coluns from "Data" matching column letters in "Categories".

The attached spreadsheet has a little input form to edit the category letters in the "Category" table.
When you refresh the 2 pivots aside, they will show the same column field values.

Re: Pivot Table Redundancy - I think "[Solved]"

PostPosted: Sun Dec 01, 2019 7:53 pm
by AlexandraNyx
Appreciate your knowledge my friend!