Hello,
I am using pivottables quite a lot in many excel files.
These files often contain one list of results, suitable for pivotting.
Typically, about 10 "row headers" and 50 values per row.
Quite often I need to compare the data from these excel files (scenarios).
Till today, I used brute force. I calculated in a macro a "difference" table. This is against all my development rules.
I tested now "multiple consolidation ranges" (for the first time), but it doesn't suit my purpose (if any!). That's a strange thing.
As an alternative, I created a macro to build a comparison list that simply merges two or more scenarios. Of course, I included an additional column that indicates the name of the scenario. Using a field option "Show data as (Difference From)" makes it possible to display the difference between the two scenarios (or even more).
Seems ok? No, I am still not satisfied.
I want to make it easy for the end-users to configure their own scenario comparisons.
So, they will choose the value to compare, the fields for grouping, ...
But most people don't know a lot (or enough) about pivottables and won't be able -for example- to chose the base item for comparison.
In addition, building the merging list still needs some programming and customization.
My question: how would you simplify list comparison, using the existing tools in excel, specially the pivottables.
Thanks a lot
I am using pivottables quite a lot in many excel files.
These files often contain one list of results, suitable for pivotting.
Typically, about 10 "row headers" and 50 values per row.
Quite often I need to compare the data from these excel files (scenarios).
Till today, I used brute force. I calculated in a macro a "difference" table. This is against all my development rules.
I tested now "multiple consolidation ranges" (for the first time), but it doesn't suit my purpose (if any!). That's a strange thing.
As an alternative, I created a macro to build a comparison list that simply merges two or more scenarios. Of course, I included an additional column that indicates the name of the scenario. Using a field option "Show data as (Difference From)" makes it possible to display the difference between the two scenarios (or even more).
Seems ok? No, I am still not satisfied.
I want to make it easy for the end-users to configure their own scenario comparisons.
So, they will choose the value to compare, the fields for grouping, ...
But most people don't know a lot (or enough) about pivottables and won't be able -for example- to chose the base item for comparison.
In addition, building the merging list still needs some programming and customization.
My question: how would you simplify list comparison, using the existing tools in excel, specially the pivottables.
Thanks a lot
