Creating Charts in Excel - Automatic

[tbg130]

Hi All,

I've been working on a project that takes data from Access and generates a variety of reports in Excel. I'd like to automatically generate a number of bar/pie charts in that worksheet based on summary data. My problem is that I have a list of about 40 data records in rows, and would like to chart (in horizontal bar chart for example) the TOP 5 producers, and have a 6th entry for SUM OF ALL OTHER PRODUCERS.

Table is as follows:
CountryName Units Value
Country 1 100,000.00 $250,000.00
Country 2 9,000.00 $240,000.00
Country 3 8,000.00 $230,000.00
Country 4 7,000.00 $220,000.00
Country 5 6,000.00 $210,000.00
Country 6 5,000.00 $200,000.00
Country 7 4,000.00 $190,000.00
Country 8 3,000.00 $180,000.00
Country 9 2,000.00 $170,000.00
Country 10 1,000.00 $160,000.00
Country 11 0.00 $0.00
Country 12 0.00 $0.00
Country 13 0.00 $0.00
Country 14 0.00 $0.00

So, in the example above, I'd like to select Country 1, Country 2, Country 3, Country 4 and Country 5 as the TOP 5 producers, and the VALUE (3rd column) for each of those countries... Then, takes Country 6 - Country 14 as OTHER and the sum of Values (column 3)...

How can I use VBA to do this and generate a nice and simple bar chart?

Any and all help is HUGELY appreciated as I am running tight on time and deadlines...

Thanks in Advance to ALL!

Tyler

 

[SkipVought]

Hi,

Here's a NON vba solution using spreadsheet formulas and a PivotTable. You could essentially code this solution.

1) Sort by Value descending

2) add a column for rank
[tt]
=IF(ROW()>6,6,ROW()-1)
[/tt]
3) add a column for the computed country
[tt]

[/tt]


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[SkipVought]

sorry, I hit SUBMIT in error

continued...

[tt]
=IF(Rank<6,CountryName,"Other")
[/tt]
4) Pivot on the New Country column, sum of Value.
result...
[tt]
Sum of Value
Country Total
Country 1 $250,000
Country 2 $240,000
Country 3 $230,000
Country 4 $220,000
Country 5 $210,000
Other $900,000
Grand Total $2,050,000
[/tt]


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[tbg130]

Thanks Skip;

Are you suggesting that a Pivot Table could be created with VBA code and automatically set the Pivot Table to generate the result in that manor then create a chart on that?

I've never done Pivot Tables, and especially not through VBA...

Any help you could provide there?

Thanks for the great idea!

 

[SkipVought]

Turn on your macro recorder and have at it!

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}