Easy charting question (hopefully)

Hi All,

This should be a fairly simple one but I can not find through Google or
Microsoft on how to accomplish it.

My rows of data have 14 columns.  One of those columns I would like to
do a count on, so for example the COLUMN of data looks like:

Apples
Apples
Apples
Oranges
Pears
Pears
Zuchini

I want a chart that shows a count for each one, so there would be 3
apples, 1 oranges, 2 pears, and 1 zuchini.  This would then be in a bar
chart.

Does that make sense?

Thanks for your help,
SD

0
5/10/2006 11:09:46 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
720 Views

Similar Articles

[PageSpeed] 58

Perhaps this:

Sub Test()
Dim r As Range, c As Range
Dim cht As Chart
Dim s As Series
Dim ws As Worksheet
Dim coll As Collection
Dim i As Integer
Dim val As Integer, MaxVal As Integer

Set coll = New Collection
Set ws = Sheets("Inventory")
With ws
    Set r = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
On Error Resume Next
For Each c In r.Cells
    coll.Add c.Value, c.Value
Next
On Error GoTo 0
Set cht = ws.ChartObjects(1).Chart
With cht
    For i = 1 To .SeriesCollection.Count
        cht.SeriesCollection(1).Delete
    Next
    For i = 1 To coll.Count
        Set s = .SeriesCollection.NewSeries
        val = Application.CountIf(r, coll(i))
        s.Values = val
        MaxVal = IIf(MaxVal < val, val, MaxVal)
        s.Name = coll(i)
        s.Border.LineStyle = xlNone
        s.HasDataLabels = True
        With s.Points(1).DataLabel
            .Font.Color = vbRed
            .Text = coll(i)
        End With
    Next
    With .Axes(xlCategory)
        .HasTitle = True
        .AxisTitle.Characters.Text = "Produce Inventory"
    End With
    With .Axes(xlValue)
        .HasTitle = True
        .AxisTitle.Characters.Text = "Tonnes"
        .MaximumScale = 1.5 * MaxVal
        .MinimumScale = 0
    End With
End With
End Sub

Regards,
Greg

"aether8203@yahoo.com" wrote:

> Hi All,
> 
> This should be a fairly simple one but I can not find through Google or
> Microsoft on how to accomplish it.
> 
> My rows of data have 14 columns.  One of those columns I would like to
> do a count on, so for example the COLUMN of data looks like:
> 
> Apples
> Apples
> Apples
> Oranges
> Pears
> Pears
> Zuchini
> 
> I want a chart that shows a count for each one, so there would be 3
> apples, 1 oranges, 2 pears, and 1 zuchini.  This would then be in a bar
> chart.
> 
> Does that make sense?
> 
> Thanks for your help,
> SD
> 
> 
0
GregWilson (42)
5/11/2006 6:18:01 AM
You won't need to set the axis titles every time. So the following could be 
simplified. Change:

    With .Axes(xlCategory)
        .HasTitle = True
        .AxisTitle.Characters.Text = "Produce Inventory"
    End With
    With .Axes(xlValue)
        .HasTitle = True
        .AxisTitle.Characters.Text = "Tonnes"
        .MaximumScale = 1.5 * MaxVal
        .MinimumScale = 0
    End With

To:

With .Axes(xlValue)
       .MaximumScale = 1.5 * MaxVal
       .MinimumScale = 0
End With

Greg



"Greg Wilson" wrote:

> Perhaps this:
> 
> Sub Test()
> Dim r As Range, c As Range
> Dim cht As Chart
> Dim s As Series
> Dim ws As Worksheet
> Dim coll As Collection
> Dim i As Integer
> Dim val As Integer, MaxVal As Integer
> 
> Set coll = New Collection
> Set ws = Sheets("Inventory")
> With ws
>     Set r = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
> End With
> On Error Resume Next
> For Each c In r.Cells
>     coll.Add c.Value, c.Value
> Next
> On Error GoTo 0
> Set cht = ws.ChartObjects(1).Chart
> With cht
>     For i = 1 To .SeriesCollection.Count
>         cht.SeriesCollection(1).Delete
>     Next
>     For i = 1 To coll.Count
>         Set s = .SeriesCollection.NewSeries
>         val = Application.CountIf(r, coll(i))
>         s.Values = val
>         MaxVal = IIf(MaxVal < val, val, MaxVal)
>         s.Name = coll(i)
>         s.Border.LineStyle = xlNone
>         s.HasDataLabels = True
>         With s.Points(1).DataLabel
>             .Font.Color = vbRed
>             .Text = coll(i)
>         End With
>     Next
>     With .Axes(xlCategory)
>         .HasTitle = True
>         .AxisTitle.Characters.Text = "Produce Inventory"
>     End With
>     With .Axes(xlValue)
>         .HasTitle = True
>         .AxisTitle.Characters.Text = "Tonnes"
>         .MaximumScale = 1.5 * MaxVal
>         .MinimumScale = 0
>     End With
> End With
> End Sub
> 
> Regards,
> Greg
> 
> "aether8203@yahoo.com" wrote:
> 
> > Hi All,
> > 
> > This should be a fairly simple one but I can not find through Google or
> > Microsoft on how to accomplish it.
> > 
> > My rows of data have 14 columns.  One of those columns I would like to
> > do a count on, so for example the COLUMN of data looks like:
> > 
> > Apples
> > Apples
> > Apples
> > Oranges
> > Pears
> > Pears
> > Zuchini
> > 
> > I want a chart that shows a count for each one, so there would be 3
> > apples, 1 oranges, 2 pears, and 1 zuchini.  This would then be in a bar
> > chart.
> > 
> > Does that make sense?
> > 
> > Thanks for your help,
> > SD
> > 
> > 
0
GregWilson (42)
5/11/2006 6:43:01 AM
Put a title on that column, "Food". Select the range, and from the Data 
menu, crate a pivot table. Put the Food field into the Row area, and another 
copy of it into the Data area. The result looks like this (hope it pastes 
okay):

      Count of Food
      Food Total
      Apples 3
      Oranges 1
      Pears 2
      Zuchini 1
      Grand Total 7


You can chart this data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

<aether8203@yahoo.com> wrote in message 
news:1147302586.327017.294850@y43g2000cwc.googlegroups.com...
> Hi All,
>
> This should be a fairly simple one but I can not find through Google or
> Microsoft on how to accomplish it.
>
> My rows of data have 14 columns.  One of those columns I would like to
> do a count on, so for example the COLUMN of data looks like:
>
> Apples
> Apples
> Apples
> Oranges
> Pears
> Pears
> Zuchini
>
> I want a chart that shows a count for each one, so there would be 3
> apples, 1 oranges, 2 pears, and 1 zuchini.  This would then be in a bar
> chart.
>
> Does that make sense?
>
> Thanks for your help,
> SD
> 


0
jonxlmvpNO (4558)
5/11/2006 11:08:42 AM
Reply:

Similar Artilces:

Calendar easy to move from computer to computer?
Is there a way besides a Palm pilot to transfer all contacts, and calendar info from work to the home computer as a daily task? The problem is my palm pilot does not carry over certain fields in contacts that transfer from work to home. Is there a web based outlook? In Calendar, the color labeling does not transfer either. Looking for an easy way to have both computers updated in the same way. Thanks! .. ...

CFileDialog question
I am using CFileDialog and using this is it possible to capture right click? For my requirement I do not want to process right click. Please let me know if there is a way to do this... Thanks Suchrithaa ...

Filter Question 05-20-07
I have a List Box from a query on my form that has a field ClientID and 2 other fields and on my form I have one ClientID, How can I filter the list box to only show the two other fields with the same ClientID in the list Box, Thanks for any Help ...

help with charting please
Hello, I am trying to develop a complex chart & need help and advice please. I have 4 data series that I want to plot on the Y axis. The X axis is the same for all 4 series. The first 2 series have values between �0 and �6, and I want to plot them as vertical bars against the left-hand (primary) Y axis. The 3rd & 4th series have values between -10.0 and +25.0, and I want to plot them as a line chart against the right-hand (secondary) Y axis. Can I do all this on one chart, and if so how do I go about it ? Thanks KK PS I have Excel 2007 KRK:- Question ...

Question about using UNION in stored procedure
Hi, ** Please find scripts at the end of this message ** I created a table that I use to define upsell products. As you'll see in the scripts, it's a very simple two field table i.e. ProductID, UpsellProductID both linking back to the Products table. My question is this: Due to the nature of products in my business model, these upsell relationships are reciprocal i.e. someone buying Product A should be offered Product B as an upsell. Also someone buying Product B should be offered Product A as an upsell. Therefore, whenever I run a query, I need to search both fiel...

Pictures in Charts
I am using pictures as fill in my Excel charts. The problem I am having is that after I "Select" the picture in in the Fill Effects - Picture tab, the other options are not available. Ex: Format - Stretch and especially the Apply to - Sides - Front - End. I want the picture to be one continuous image and right now it is replicated on the sides Without sending me code - obviously, I am not at that skill level - can anyone tell me how to fix my problem Thanks in advance Also if someone would answer Jen regarding the spot color in Excel - I would be really interested in that mysel...

macro/worksheet/chart interaction
I am running a macro which simulates an engineering application. The macro essentially time steps, sending the data at each time step to a worksheet, and a chart is set up to display the data in graphical form. Problem - although the worksheet is updated at each time step, the chart only updates when the macro has finished executing - and I want to see the data being graphed as the simulation proceeds - is there anyway of getting the macro to pause while the chart is updated - some kind of handshake between EXCEL and the macro? (At the moment the only way I can do this is to put a STOP in t...

Chart type options
Good day, I would like to know if it's possible to change the columns into another figure like let's say the shape of a house. Thanks See http://peltiertech.com/Excel/ChartsHowTo/CustomStacks.html http://www.internet4classrooms.com/excel_picto_chart.htm http://www.lttechno.com/links/pictographs.html for more search Google with: a) Excel chart picture OR b) Excel pictograph best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "ekkeindoha" <ekkeindoha@discussions.microsoft.com> wrote in message news:C55DB27A-7ABF-...

SendMessage Question/Problem
I have a dialog box with a toolbar subclassed in it that has a combo box. I have embedded the toolbar in a static control on the dialog box. When an item in the combo box is selected, I am getting the OnSelChange message being triggered in the subclassed tool bar class. I then get the item that was selected and call GetOwner()->SendMessage(WM_NOTIFY......) In the dialog box I have a ON_NOTIFY message command for the IDR_TOOLBAR. when I debug the OnSelChange method in the subclassed toolbar class, I can see it setting up the call, but when I place my cursor and do run to cursor in the O...

Excel question #11
How do I lock a row of cells so that anything I type in them will be in upper case? ...

Report charts
When I run reports in CRM the charts don't appear. Any ideas? Hey Slim, Ok this is a weird one, havent seen this before :c). For starters lets check something: 1. Try setting the CRM server resolution to 1024X768. 2. Restart all the crystal services. Let me know if htis resolves the issue and also if it does not. Will see what more i can dig for you on this. Jibran "Slim" wrote: > When I run reports in CRM the charts don't appear. Any ideas? ...

Issues with Pivot Chart
Hello Everybody.. I have a workbook with a pivot chart and a pivot table.. The workbook contains the details about the sales made for that particular assessment period. The Pivot chart sources the data from the data sheet containing sales for all levels.. The chart has Chart fields for all levels. The issue is when one corresponding level is selected, the chart gets updated to point to the data corresponding to that level. But the other pivot chart-fields still have data pertaining to all the levels.. Its fine as the chart points to the data sheet, which contains data for all the levels. I...

easy OnLButtonDblClk() question
Hi, I've created a MFC application. It has the standard window where information is displayed. I have recently copied some code from another applcation someone made for me, so that I have a dockable window that attaches to the main document. The code for this class it as follows: class CMarkerZoom : public CWnd { // Construction public: CMarkerZoom(); // Overrides // ClassWizard generated virtual function overrides file://{{AFX_VIRTUAL(CMarkerZoom) file://}}AFX_VIRTUAL // Implementation public: virtual ~CMarkerZoom(); // Generated message map functions protected: file://{{AFX_...

Quick Question #3
I have a workbook that is basically made up of 2 worksheets, a importing data sheet and a chart. Everything works great, timely refreshes and its rather simple. The problem I have is this: The chart is basically a seating chart and does not change at all(static). The 2nd worksheet continuosly updates the chart and will sometimes add new values(dynamic). Well, my formulas that I have for each cell points to the dynamic sheet and the values will change on occasion, unpredictablely. How can I write the formulas pointing to certain values in the data sheet(dynamic) and lock the cells. Then any ne...

flow chart
does anyone have a template for a flow chart for excel 2003? I'm looking for a flow chart template that ahs one box at the top then the next section has 3 boxes and the 3 boxes continue down the page. Hope this makes sense. x x x x x x x x x x x x x Thanks Jeannette ...

How to make a checkbox on a chart invisible when it is clicked?
How to make a checkbox on a chart invisible when it is clicked? I have some code which hides a coulmn when a checkbox is clicked. Now my users want not only hide the column, but also get the checkbox itself to "go away" - I translate it as "make invisible " Now I have the code: Sub Check_Box_Click() Sheets("Summary_Worksheet").Select Select Case Application.Caller Case "cbI40" Columns("I").Select Case "cbJ40" Columns("J").Select Case "cbK40" Columns("K").Select Case Else End Select If S...

Change series in a chart
Hi guys, My first post. I have data for residents of the care home that I work at. One example is their monthly weights. I presently have the data in the following form: jan feb mar apr may Resident 1 108 109 107 105 106 Resident 2 220 225 219 226 222 I would like to have a control box or something on the line chart of a resident's weights so that I can choose which resident's monthly weights to display. The x-axis is the month and the y-axis the weight. I can't seem to figure out how to get a macro to do this. Thanks a bunch Stuart -- trispirit3...

Quick Import Excel Chart to Word?
Is there a quick way to input Excel charts into a word document? I have 540 charts that I need to have in an appendix of a document. I'm opening to any suggestions that you may have. One thought that I had is to create a VBA script to essentially do a copy and paste 540 times. But, I have a feeling there is an easier and/or faster way to get the job done. Thanks in advance for your help. -Matt Thanks Jon. Your website appears to be just what the doctor ordered. Copying charts as pictures seem to have some nice advantages over OLE objects. -Matt >-----Original Message----...

Apparent Excel 2007 chart HasTitle race condition
The macro below works correctly, i.e. creates a chart without a title, with Excel 2003, but the chart has a title with Excel 2007 SP2 and the Excel 2010 beta. (The macro requires numeric data in the range A1:B10 on the first worksheet.) The macro *does* work correctly with Excel 2007 if I step through it in the debugger (thus introducing delays between each statement), or if I introduce additional statements before the ch.HasTitle statement (in particular *two* DoEvents calls seems to work). My company's application that uses Excel for reports has larger macros contain...

Charting multiple column data
I have a sheet in Excel 2003 with a number of columns, all of which follow the same, repeated pattern: Day of week Date (dd/mm) Volume Blank column This is then repeated over each month. So, I have 4 columns for July, August, September etc. Each of the months have their own individual line chart, showing date / volume. What I want to do is display a month on month, running total chart, i.e. the one line chart that shows all volumes to date. However, I can't get this done the way my data is stored. If I select multiple columns as my source data, the sheet shows the one line for each ...

Conditional Formatting in charts?
I want to make the highest value in a chart display BOLD type. I know how to do this in a group of cells using conditional formatting -- =MAX($C$14:$C$23), then set font to bold. But conditional formatting doesn't work on a pivot table once it's refreshed. My chart was created from a pivot table. Is there any way to do this without creating a new chart from scratch? ...

easy transfer
I want to use easy transfer from my XP to a new PC with Windows 7. Can I just transfer what is on my old PC to a flash drive then just plug the flash drive into the new PC and then start the easy transfer on the new PC and just get the information off of the flash drive? Mike Givens wrote: > I want to use easy transfer from my XP to a new PC with Windows 7. > Can I just transfer what is on my old PC to a flash drive then just plug > the flash drive into the new PC and then start the easy transfer on the > new PC and just get the information off of the fla...

excel 2007 line weight in legend does not match the chart
In excel 2007 the line weight in the legend does not appear to reflect the line weight in the chart. That is when I adjust the weight of a line in a line chart this is not shown as a change in the weight of the line in the legend. This is a problem because I tend to use charts with 'thick lines' and 'thin lines' and the legend does not differentiate on the basis of the line weight - only on the use of markers and whether or not the line is dashed. This is exacerbated by the fact that my charts are in black and white and have multiple plotting points that limit the utili...

Embedded Chart Versus Chart
I've created a class module to capture a Shift+Click on a pivot chart, which then opens the data associated with that point. It works great on pivot charts on their own sheet, but when I use the same code on an embedded chart, it fails (error code -2147417848). Everything seems to work fine, it derives the same row and column values as needed, but the ShowDetail property is unavailable in the embedded charts. Anyone have any insight? james Igoe || james.igoe@gmail.com || http://code.comparative-advantage.com I eventually solved my problem, simply using "On Error Resume Next&...

Convert xlusrgal.xls to Excel 2007 Chart Templates
I have some users who used the xlusrgal.xls file stored in C:\Documents and Settings\<user name>\Application Data\Microsoft\excel to store their own chart templates in Excel 2003. This doesn't seem to work in Excel 2007, and I see they have replaced it by Chart templates (.crtx). These chart templates need to be stored in a 'Charts' folder underneath the regular template path. The issue I have is that we lock down the template path for the firm (both user and shared), but we want to allow users to create their own chart templates by storing them to their profile as ...