JDS_916
07-27-2014, 04:40 PM
Hello to everyone,
I have exported sharepoint list data to Excel to produce a number of pivot tables and charts. The list has three columns (‘country’, ‘article’ and ‘finding’). The ‘article’ column is a choice column configured for multiple selections, so two or more values are shown in Excel separated by a semi colon and a hash (;#). For example:
Portugal Article 1 shortcoming
Spain Article 2 shortcoming
Italy Article 3;#Article 8 shortcoming
Poland Article 4 observation
UK Article 2 observation
France Article 25 observation
Germany Article 1;#Article 4 shortcoming
Sweden Article 3;#Article 1 observation
Denmark Article 1 shortcoming
Norway Article 1 shortcoming
When this data is shown in my clustered column chart, it correctly gives the axis fields/categories as Article 1 through 25. The chart’s values are represented as findings. The particular chart shows the number of findings against a particular article. So, with the example data above, the chart would show the following:
Article 1 with 3 findings
Article 2 with 2 findings
Article 3;#Article 8 with 1 finding
Article 4 with 1 finding
Article 1;#Article 4 with 1 finding
Article 3;#Article 1 with 1 finding
Article 25 with 1 finding
However, although I want this chart for certain statistics, I also want a true count of individual articles. As you can see, article 1 really has 4 findings not 3, but as one article 1 is associated with article 4, it does not get counted. This goes for article 3 also, which really has 2 findings but would not be displayed as an individual column because they are associated with other articles.
Is there any way that Excel, either through a formula or VBA, can show the chart with individual article counts. My problem is the multiple values.
Regards,
JDS_916
I have exported sharepoint list data to Excel to produce a number of pivot tables and charts. The list has three columns (‘country’, ‘article’ and ‘finding’). The ‘article’ column is a choice column configured for multiple selections, so two or more values are shown in Excel separated by a semi colon and a hash (;#). For example:
Portugal Article 1 shortcoming
Spain Article 2 shortcoming
Italy Article 3;#Article 8 shortcoming
Poland Article 4 observation
UK Article 2 observation
France Article 25 observation
Germany Article 1;#Article 4 shortcoming
Sweden Article 3;#Article 1 observation
Denmark Article 1 shortcoming
Norway Article 1 shortcoming
When this data is shown in my clustered column chart, it correctly gives the axis fields/categories as Article 1 through 25. The chart’s values are represented as findings. The particular chart shows the number of findings against a particular article. So, with the example data above, the chart would show the following:
Article 1 with 3 findings
Article 2 with 2 findings
Article 3;#Article 8 with 1 finding
Article 4 with 1 finding
Article 1;#Article 4 with 1 finding
Article 3;#Article 1 with 1 finding
Article 25 with 1 finding
However, although I want this chart for certain statistics, I also want a true count of individual articles. As you can see, article 1 really has 4 findings not 3, but as one article 1 is associated with article 4, it does not get counted. This goes for article 3 also, which really has 2 findings but would not be displayed as an individual column because they are associated with other articles.
Is there any way that Excel, either through a formula or VBA, can show the chart with individual article counts. My problem is the multiple values.
Regards,
JDS_916