I have a single coulmn which may be of varied length and consists of any of
six potential values. e.g

2
3
4
2
5
6
1
1
6
3
etc


I need to be able to count the number of each value which appears in the
column and return the counts to a set of cells
can you help please

=countif(A:A,1)

etc

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alan M" wrote in message
...
I have a single coulmn which may be of varied length and consists of any
of
six potential values. e.g

2
3
4
2
5
6
1
1
6
3
etc


I need to be able to count the number of each value which appears in the
column and return the counts to a set of cells
can you help please

Ooops sorry Bob I meant to say that I need to do this using VBA

"Alan M" wrote:

I have a single coulmn which may be of varied length and consists of any of
six potential values. e.g

2
3
4
2
5
6
1
1
6
3
etc


I need to be able to count the number of each value which appears in the
column and return the counts to a set of cells
can you help please

for i = 1 to 6
cells(i,"D").Value = Application.Countif(columns(1),i)
Next

--
Regards,
Tom Ogilvy

"Alan M" wrote in message
...
Ooops sorry Bob I meant to say that I need to do this using VBA

"Alan M" wrote:

I have a single coulmn which may be of varied length and consists of any
of
six potential values. e.g

2
3
4
2
5
6
1
1
6
3
etc


I need to be able to count the number of each value which appears in the
column and return the counts to a set of cells
can you help please

Tom,
I am also interested in doing something like this. However, I do not
understand your solution. What is the "D" for?
Also, is there a way to do this such that the code determines which entries
are present and only counts those. For example, if in Alan's example, the
number 3 was never entered, the code would only count the number of times 1,
2, 4, 5, and 6 were entered in the range?

TIA

"Tom Ogilvy" wrote:

for i = 1 to 6
cells(i,"D").Value = Application.Countif(columns(1),i)
Next

--
Regards,
Tom Ogilvy

"Alan M" wrote in message
...
Ooops sorry Bob I meant to say that I need to do this using VBA

"Alan M" wrote:

I have a single coulmn which may be of varied length and consists of any
of
six potential values. e.g

2
3
4
2
5
6
1
1
6
3
etc


I need to be able to count the number of each value which appears in the
column and return the counts to a set of cells
can you help please

The D stands for column D

for i = 1 to 6
If Application.Countif(Columns(1),i) 0 Then
j = j + 1
cells(j,"D").Value = "Count for i = " &
Application.Countif(columns(1),i)
End If
Next


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Papa Jonah" wrote in message
...
Tom,
I am also interested in doing something like this. However, I do not
understand your solution. What is the "D" for?
Also, is there a way to do this such that the code determines which
entries
are present and only counts those. For example, if in Alan's example, the
number 3 was never entered, the code would only count the number of times
1,
2, 4, 5, and 6 were entered in the range?

TIA

"Tom Ogilvy" wrote:

for i = 1 to 6
cells(i,"D").Value = Application.Countif(columns(1),i)
Next

--
Regards,
Tom Ogilvy

"Alan M" wrote in message
...
Ooops sorry Bob I meant to say that I need to do this using VBA

"Alan M" wrote:

I have a single coulmn which may be of varied length and consists of
any
of
six potential values. e.g

2
3
4
2
5
6
1
1
6
3
etc


I need to be able to count the number of each value which appears in
the
column and return the counts to a set of cells
can you help please

The D means write the results in Column D.


You can loop through the cells and add the items to a collection, setting
the index property. This will prevent duplicates. Then use that list to
write the data.


Sub IJKL()
Dim nodupes As New Collection

Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
On Error Resume Next
For Each cell In rng
nodupes.Add cell.Value, Key:=cell.Text
Next
On Error GoTo 0
i = 0
For Each itm In nodupes
Debug.Print itm
i = i + 1
Cells(i, "D") = itm
Cells(i, "E").Formula = "=Countif(" & _
rng.Address & "," & Cells(i, "D").Address & _
")"
' or
' Cells(i, "E").Value = Application.CountIf( _
' rng, itm)
Next
End Sub

If you want the list sorted, throw in a sort as the last statement.

--
Regards,
Tom Ogilvy


"Papa Jonah" wrote in message
...
Tom,
I am also interested in doing something like this. However, I do not
understand your solution. What is the "D" for?
Also, is there a way to do this such that the code determines which
entries
are present and only counts those. For example, if in Alan's example, the
number 3 was never entered, the code would only count the number of times
1,
2, 4, 5, and 6 were entered in the range?

TIA

"Tom Ogilvy" wrote:

for i = 1 to 6
cells(i,"D").Value = Application.Countif(columns(1),i)
Next

--
Regards,
Tom Ogilvy

"Alan M" wrote in message
...
Ooops sorry Bob I meant to say that I need to do this using VBA

"Alan M" wrote:

I have a single coulmn which may be of varied length and consists of
any
of
six potential values. e.g

2
3
4
2
5
6
1
1
6
3
etc


I need to be able to count the number of each value which appears in
the
column and return the counts to a set of cells
can you help please

Tom,
I think I understand the concept now. However, I am experimenting with your
code and vba does not seem to like your "For each cell in rng" statement.
I have created a statement:
dim rng as range

Should I have not created the dim? or does the For statement need to be
tweaked?

Thanks

"Tom Ogilvy" wrote:

The D means write the results in Column D.


You can loop through the cells and add the items to a collection, setting
the index property. This will prevent duplicates. Then use that list to
write the data.


Sub IJKL()
Dim nodupes As New Collection

Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
On Error Resume Next
For Each cell In rng
nodupes.Add cell.Value, Key:=cell.Text
Next
On Error GoTo 0
i = 0
For Each itm In nodupes
Debug.Print itm
i = i + 1
Cells(i, "D") = itm
Cells(i, "E").Formula = "=Countif(" & _
rng.Address & "," & Cells(i, "D").Address & _
")"
' or
' Cells(i, "E").Value = Application.CountIf( _
' rng, itm)
Next
End Sub

If you want the list sorted, throw in a sort as the last statement.

--
Regards,
Tom Ogilvy


"Papa Jonah" wrote in message
...
Tom,
I am also interested in doing something like this. However, I do not
understand your solution. What is the "D" for?
Also, is there a way to do this such that the code determines which
entries
are present and only counts those. For example, if in Alan's example, the
number 3 was never entered, the code would only count the number of times
1,
2, 4, 5, and 6 were entered in the range?

TIA

"Tom Ogilvy" wrote:

for i = 1 to 6
cells(i,"D").Value = Application.Countif(columns(1),i)
Next

--
Regards,
Tom Ogilvy

"Alan M" wrote in message
...
Ooops sorry Bob I meant to say that I need to do this using VBA

"Alan M" wrote:

I have a single coulmn which may be of varied length and consists of
any
of
six potential values. e.g

2
3
4
2
5
6
1
1
6
3
etc


I need to be able to count the number of each value which appears in
the
column and return the counts to a set of cells
can you help please