Diagram - group by

[Jorgensen]

I have an table that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Group 1[/TD]
[TD]Subject A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Subject B[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Subject C[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]Subject D[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Subject E[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Subject G[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Subject Z[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]Subject Y[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

i can't figure out how to create a diagram, that groups the data via the Group column / and i don't know if it's possible at all :-/

it seems i can't insert images from dropbox so i had to add a link
but here is a sketch that shows what i'm trying to achieve
https://www.dropbox.com/s/7hef1hhv2q5ecez/Capture.PNG?dl=0

thanks
jorgensen

 

[etaf]

If you select the area and insert a clustered bar chart
you will get close to what you are after

If you merge the group info - then it can work as the diagram

https://www.dropbox.com/s/9t22g37181rvml3/Charts.xlsx?dl=0

 

[Jorgensen]

ah thanks etaf

i will try that!

thanks
jorgensen

 

[thisoldman]

Another option is to insert the cluster titles as text boxes.

 

[Jorgensen]

hi thisoldman

is it possible to group by 'group' AND sort by the number?
as i would like to keep it as flexible as possible

thanks

first i was hoping that i could have one column with the group name, so i somehow could SELECT ALL FROM table GROUP BY group SORT BY number - but that's more like MySQL

[/QUOTE]

 

[thisoldman]

Which values are you using to sort? I'm not clearly understanding what you want.

Perhaps these tips will help a bit.

To cluster bars or columns, empty (no data), dummy rows are needed.

Text box content can be a cell value. Select the text box, then go to the formula bar, type an equals sign and enter the cell reference by typing or pointing: =Sheet1!$A$2. The sheet name is required.

It may be that what you want is possible only with VBA.

 

[Jorgensen]

hi thisoldman

this is really going in the right direction

what i ment was if it is possible to sort groups by their value like this:

Group 1
Subject C 4
Subject B 3
Subject D 2
Subject A 1

ps. how did you add the "group 1" and "goup 2" labels in the example?
ps. i guess that you all have seen what m yamin has created using excel:
Impress your Boss with Excel Infographics – ExcelGO
quite impressive - but also very complicated i guess.

 

[thisoldman]

You can follow a work pattern similar to the advice given in the link from your last post. Rather than the four divisions given in the article, I usually divide a worksheet or a workbook into three sections: data, a combined calculation and logic section, and a presentation section.

I use formulas to copy the source data to the block of cells I chart.

The group labels are text boxes, from the Insert menu. This is not a robust method if the number of items in each group will change. It does work well enough for a one-off chart.

 

[Jorgensen]

hi thisoldman

i will try that - to separate the worksheet into data, logic section, presentation - sounds like a good idea.

can you show a simple example how you use formulas to copy the source data to the ?

i'm sorry for all these noob questions - i am a noob in excel - but have a deadline on monday :-/

can you recommend a site / blog, or book that would be a good place to start?

thank you for your patience.
Jorgensen

 

[thisoldman]

For a Monday deadline, if copy and paste will work, I'd use that method to organize data rather than worry about formulas. Or, use simple formulas, "=H2".

A lot of my learning comes with just knowing that something can be done with Excel. I see something useful or elegant, and I want to copy it for my own purposes. I'll note that I use Excel mostly without dollar signs, my work is in chemistry and engineering.

The books and videos offered from the MrExcel.com website are generally very good. Bill Jelen's Youtube presentations are also often recommended, but I prefer books and text.

Jon Peltier's website, http://peltiertech.com/, is also very useful for charting techniques. He writes articles that are dense with technical information, a style that might be intimidating for a new user.

For articles written in a more conversational tone, try chandoo.org, http://chandoo.org/wp/welcome/

It's wonderful to see that you are enthusiastic about learning. I truly hope I've helped you.