Stacked Bar Chart - Data Structure Issues

[mattphillip]

Hoping for some advice here. I've designed a stacked bar chart where each dimension totals 100% using a basic inline table for development purposes. I've now received the proper data set from the client (see extract below). The problem lies in the structure of the data, with each question as field, with the answers values within it, rather than having individual 'Question' and 'Answer' fields. Obviously, my stacked bar chart won't work in its current form (as it assumes the presence of two fields) and I'm now faced with scripting Excel to re-arrange the data into an appropriate structure OR (and I've got my fingers crossed) someone has any clever ideas on producing a stacked bar chart using the data in its current format (headings refer to question and remaining values are the answers)?

Memorising	Applying	Analysing	Evaluating	Forming	Changes to work
Often	Very often	Very often	Often	Often	Sometimes
Sometimes	Often	Often	Sometimes	Often	Sometimes
Often	Very often	Sometimes	Often	Very often	Sometimes
Sometimes	Sometimes	Often	Often	Very often	Never
Often	Often	Often	Very often	Very often	Sometimes
Sometimes	Very often	Often	Often	Often	Sometimes
Often	Very often	Very often	Often	Very often	Sometimes
Often	Very often	Sometimes	Often	Often	Very often

Development Stacked Bar Chart:

Any help would be greatly appreciated.

Matt

[morganaaron]

Hi Matt,

I don't think you'll be able to create that sort of chart or use the data in that way without some sort of transformation.

I've attached an app example here - I manually added an ID column (you could use a quick autonumber in excel by inserting a column) to call that a response ID, then loaded it as a Cross Table.

You can then use your Question and Answer fields as dimensions and Count the IDs for the number of responses.

I don't profess that this is the easiest or best solution, there may well be a better one out there!

Aaron