How to Create a Matrix Chart with Columns 'Grouped By'

[PEM000]

Greetings All,

I am attempting to create a chart that is 'like a bar chart' to report on survey results that includes only 6 partners. I know which partner responded to this particular question and I want to create a chart that shows the options selected in specific columns so it is easy to see how many partners selected that option and look at the y-axist to see who is who.

Is there a built-in chart that does this? Is there an Add-in I can purchase? Is there a Macro that anyone knows of that will do this?

Every chart I have been able to create 'collapses' the x-axis so that, yes, you can figure out who selected which options, but it is not as visually apparent.

The example mocked up Chart and the data table are below in .png images.

Thanks very much for any help or insight!!

Paul

 

[Sergius]

Hello! This can be done using an additional table (see the photo).

This table is built as follows: Pillars with retreats are added, as well as data from the main table. Pay attention to the location of the columns (see the attached table). After that, a diagram is built on this table, as well as manipulations so that it corresponds to your wishes.

Book1
	A	B	C	D	E	F	G	H	I
1		Option 1	Option 2	Option 3	Option 4
2	PARTNER A	0	0	1	0
3	PARTNER B	1	0	0	0
4	PARTNER C	0	0	1	0
5	PARTNER D	1	0	0	0
6	PARTNER E	1	1	1	0
7	PARTNER F	1	1	1	1
8
9
10	Retreat 0	Option 1	Retreat 1	Option 2	Retreat 2	Option 3	Retreat 3	Option 4	Retreat 4
11	1	0	1	0	1	1	0	0	1
12	1	1	0	0	1	0	1	0	1
13	1	0	1	0	1	1	0	0	1
14	1	1	0	0	1	0	1	0	1
15	1	1	0	1	0	1	0	0	1
16	1	1	0	1	0	1	0	1	0
17	1	1	0	1	0	1	0	1	0
Sheet1

Cell Formulas
Range	Formula
B11:B16	B11	=$B2
D11:D16	D11	=$C2
F11:F16	F11	=$D2
H11:H16	H11	=$E2
C11:C17,I11:I17,G11:G17,E11:E17	C11	=IF(B11=0,1,0)

 

[PEM000]

Thank you, Sergius!! I am trying to figure out how to create a macro that will do this. If I succeed, I will post it here!!

Genius. Thank you!!

Paul

 

[PEM000]

Thanks very much, Sergius!! The macro is a slog, but I will get there. Sorry I was slow to mark this as the solution.