Hi

I would like to create a chart of a column of non numeric values where the
column label would be the value and the chart would reflect the number of
occurences of the value in the column eg.

brown
blue
blue
brown
brown

this would produce two bars on the chart labled brown and blue with values
of 3 and 2 respectively.

It is the same as a GROUP BY function in SQL

Any help is really appreciated.

Thanks

Paddy

Assuming the list of colours are already entered in Range("A1:A100") then:
1. In cell B1 enter Blue
2. In cell B2 enter Brown
3. If there are more colours then continue entering (B3, B4... etc)
4. In cell C1 enter: "=CountIf($A$1:$A$100, B1)"
5. Drag the formula down to the end of the list in column B (C2 if only two
colours)
6. Select the cells containing the colour list and formulas (cols B and C)
and plot them as a Column chart

If the colour list in column A is variable in length then I think I would go
with a Dynamic Named Range in place of "A1:A100". If not familiar I can
describe.

Regards,
Greg





"Paddy" wrote:

Hi

I would like to create a chart of a column of non numeric values where the
column label would be the value and the chart would reflect the number of
occurences of the value in the column eg.

brown
blue
blue
brown
brown

this would produce two bars on the chart labled brown and blue with values
of 3 and 2 respectively.

It is the same as a GROUP BY function in SQL

Any help is really appreciated.

Thanks

Paddy

Hi Greg

Thanks, worked great.

One further question on this. Col A will have any number of entries. Is
there a way for me to get a "distinct" list of the values contained in it for
use in Col B

Thanks

Paddy

"Greg Wilson" wrote:

Assuming the list of colours are already entered in Range("A1:A100") then:
1. In cell B1 enter Blue
2. In cell B2 enter Brown
3. If there are more colours then continue entering (B3, B4... etc)
4. In cell C1 enter: "=CountIf($A$1:$A$100, B1)"
5. Drag the formula down to the end of the list in column B (C2 if only two
colours)
6. Select the cells containing the colour list and formulas (cols B and C)
and plot them as a Column chart

If the colour list in column A is variable in length then I think I would go
with a Dynamic Named Range in place of "A1:A100". If not familiar I can
describe.

Regards,
Greg





"Paddy" wrote:

Hi

I would like to create a chart of a column of non numeric values where the
column label would be the value and the chart would reflect the number of
occurences of the value in the column eg.

brown
blue
blue
brown
brown

this would produce two bars on the chart labled brown and blue with values
of 3 and 2 respectively.

It is the same as a GROUP BY function in SQL

Any help is really appreciated.

Thanks

Paddy

By "distinct list" I am assuming you meant a list of UNIQUE values contained
in column A - i.e. I am assuming there are more colours than just blue and
brown and you want to obtain a list of only unique colours.

I suggest using a Dynamic Named Range for the entries in column A. If you
are not familiar, the definition of this named range automatically adusts to
the number of entries. These are particularly usefull for charting.

***********************************
To create the Dynamic Named Range:

1. Open the Define Name dialog: Insert Name Define
2. In the "Names in workbook:" window enter: ColourListRng
3. In the "Refers to:" window enter:
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
The above formula assumes that the name of the worksheet is "Sheet1" and the
colour list starts at cell A1. Change to suit. The formula will require
adjustment if there are more data in column A below the colour list.
4. Click the Add button.
5. Close the dialog.

******************************
Macro to extract unique values from the colour list in column A
(ColourListRng). These extracted values will be copied to column B:

Sub GetUniques()
Dim r As Range, r2 As Range
Dim c As Range, cc As Range
Dim ws As Worksheet

Set r = Range("ColourListRng")
Set ws = r.Parent
Set c = r(1)
Set cc = r(1, 2)
For Each c In r
Set r2 = ws.Range(r(1), c)
If Application.CountIf(r2, c) = 1 Then
cc = c
Set cc = cc(2)
End If
Next
End Sub

********************************
Required change to formula that counts instances of each colour:
In place of the formula I gave you earlier:
=CountIf($A$1:$A$100, B1)
Substitute:
=CountIf(ColourListRng, B1)

Drag this formula down as instructed earlier.

********************************
How to test accuracy of ColourListRng:
1. Open the Define Names dialog again and click inside the "Refers to:"
window. The entries in the list and only these cells should be highlighted
with dashed lines.
2. Change the number of entries in the list and retest to ensure it adjusts
correctly. Post if it does not.


Regards,
Greg