• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto Generate Charts from large data set

T

Tripp

Member
Hello,

I am trying to determine the best method for automatically generating lots of charts from several large data sets. I have also been asked not to use macros as the spreadsheet will be sent round to a lot of people.

Essentially, the raw data comes in and I want to create charts that demonstrate how much of a material is present at a certain location. My initial thought is using MATCH INDEX to get the Sample Point, material, amount of material and date to generate the graph (there will be the same number of graphs for each new data set) but I wanted to see if there were any better suggestions from some experts.

What I would like, is to paste this data into a front page then have the charts generate.

I have uploaded a small, doctored example with and example chart. In the example I am filtering the results by 'Sample Point Description','Determinand' 'Determinand Name' and 'Result'.

Any suggestions welcome and I have been unclear please let me know.

Regards,

Tripp
 

Attachments

  • Data_Example.xlsx
    205.8 KB · Views: 10
Thanks,

But will I have to create a new pivot table for each location as I want to end up with a chart of 'date vs result' for each determinand and there are quite a few determinands.

Cheers
 
Thanks Hui,

I've been looking at Pivot Table/ Pivot chart options but they only seem to produce calculated results whereas I would like to see the actual values. Is it possible for a pivot table to return the actual value?

I had a working version with an INDEX MATCH but when I scaled it up, the file took way too long and wasn't very user friendly, which is why I'm hoping there is a pivot work around that is much faster..

Sam
 
Can you post a file with more data and a list of typical charts you require
 
Hui,

I have uploaded a small sample which should help explain my case.

I am inputting 3 columns of data (Date, Name and Result). I would then like to find the unique values of Date and Name then produce a pivot table with corresponding Results.

Using this table I would then like to produce a scatter chart for each Name with Date vs Result.

Thanks,
 

Attachments

  • Data_Example.xlsx
    110.4 KB · Views: 3
Hui,

Its close, but the Pivot Table SUMS the results whereas I want to plot the individual results for each Determinand like the charts of the second tab.

The idea is to be able to print off a chart for each name to give to the customer.

I'm looking at some VBA options atm but they get rather large rather quickly.
 
I'm a bit confused.

If you add both Date & Determinand to row label or like Hui did, make slicer selection for single Determinand. Wouldn't value be for individual result for each Determinand (though it may say Sum of... sum of single value is equal to the value...)?

If you need to you can have multiple pivot, one for each Determinand (by copy and paste).
 
Sorry, both you and Hui are showing me the way but I'm not seeing the woods for the trees. If I put "Determinand" in the column label then I'm very close to what I need.

Thank you for you patience and knowledge
 
Although you maybe correct
If there is multiple records with the same date/time for the same determinant the PT will sum the data
But if you know that isn’t the case your ok
 
Without using a VBA script I think this is my best solution. I will do some spot check on the data to see if I can find any instances of the multiple records for same date and determinant but I think its ok.

One last request for information:

Is it possible to produce multiple pivot charts from a single PT? I would like to produce a few charts all the same as in the example from Hui but for different determinants. So have both an individual selection tab and a tab that outputs several graphs for different determinants.

Currently any change which change all the charts as they are linked so not sure if this is possible.
 
I've added a macro to your original file to create the sheets based on Determinand Name. Haven't added any Pivot creation macro/ chart creation macro to it but let me know if you need that & will do as/when I can
 

Attachments

  • Data_Example.xlsm
    99.2 KB · Views: 8
Chirayu,

Thanks that's pretty cool. I'm doing an online VBA course at the moment so bits like that are always welcome.

What I am trying to think of is a method to produce several charts from a selection of determinands. If you look at the Data_Example.xlsx Uploaded by Hui where the slicer allows me to generate a new chart per determinand (one at a time), I would like to be able to make multiple selections on the slicer and for excel to generate an individual graph for each selection(ideally on a new tab, but not new tab for each one simply one tab for all the charts). Not sure if this is possible but I had a go at recording my actions to see the code but then realised this is futile as the selection is likely to change.
 
Just incase anyone wanted to know, I found my solution using a combination of Pivot Tables and the GETPIVOTDATA formula.

Once again, thanks for all the help.
 
You must log in or register to reply here.
Back
Top