Excel : stacked columns charts

[Lemox]

Hi there !

Need your help :
I'm trying to create a stacked column chart in Excel using vb6.
I want my chart to look like what you can find in the Help File called 'Example of Chart Styles' of MS Excel 2k.
Looks quite like this :

^
|
|
|
| ^
COUNT | /
| / COUNTRY
| /
| /
| /
|/
__________________> MONTH


My DataSheet contains data presented that way (3 cols) :

MONTH COUNTRY COUNT
may-04 USA 3
may-04 DE 2
may-04 FR 2
june-04 GB 1836
june-04 DE 5
june-04 FR 7
june-04 GB 6
june-04 USA 105
aug-04 USA 146
aug-04 DE 1
aug-04 FR 2
sept-04 USA 31
sept-04 DE 1
sept-04 FR 1

How can I do that ?

Any link, example or idea would be greatly appreciated.
Thanx
Lemox

 

[SkipVought]

lemox,

You said you wanted a STACKED column chart, but then drew what appears to ba a 3-axis example.

Please clarify what you want and how the country will be presented.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SkipVought]

I'm guessing that you want a 3-d column chart, with Month on the x-axis, Count on the y-axis and Country on the z-axis.

1) Do a PivotChart with Months in the ROW AREA, Country in the COLUMN AREA and Count in the DATA AREA.

Right Click n the Chart and select Chart Type - Select 3-d Column.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Lemox]

SkipVought,

Sorry for the chartType: first time with VBA-ExcelCharts.
Yes, you guess well
Ok : i'm gonna read the help about how to create a pivottable, pivotchart report...

Hope it can easily be written down in 'good-old-vb-code'.

Thanx
Lemox

 

[SkipVought]

If you are looking for a solution that does NOT use a PivotTable, it will take some massaging of your data.

1) Create a new table like this staarting in E1 assuming that your original table were starting in A1...
[tt]
MONTH USA DE FR GB
4-May 3 2 2 0
4-Jun 105 5 7 1842
4-Aug 146 1 2 0
4-Sep 31 1 1 0
[/tt]
This cn be accomplshed by using the Advanced Filter to generate a List of UNIQUE VALUES for BOTH the MONTH & the Countries. Copy 'b' transpose the Country list into ROW 1.

The Values can be done in a formula...
[tt]
=SUMPRODUCT(($B$2:$B$15=F$1)*($A$2:$A$15=$E2)*($C$2:$C$15))
[/tt]


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}