counting items in a range
 

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

counting items in a range
 

=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

counting items in a range
 

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

counting items in a range
 

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

counting items in a range
 

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

counting items in a range
 

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

counting items in a range
 

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

counting items in a range
 

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

counting items in a range
 

Possibly you have Option Explicit at the top of your module

Code works fine without it. So here are the declarations.

Option Explicit
Sub IJKL()
Dim nodupes As New Collection
Dim rng As Range, cell As Range
Dim itm As Variant, i As Long

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
i = i + 1
Debug.Print i, TypeName(itm), itm
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

--
Regards,
Tom Ogilvy



"Papa Jonah" wrote in message
...
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

counting items in a range
 

Tom's code worked fine for me.

But if you want to declare the variables, make it look something like:

Dim nodupes As New Collection
Dim rng As Range
Dim cell As Range
Dim i As Long
Dim itm As Variant



Papa Jonah wrote:

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










--

Dave Peterson

counting items in a range
 

Option Explicit was the problem. One more thing I need to learn about!
Thanks guys.
This works beautifully with numbers. I am going to add some coding to do
some vlookups so that I can do the same thing with text fields - unless you
have some insight that will keep the process easy.

I sure appreciate yall's knowledge.

"Tom Ogilvy" wrote:

Possibly you have Option Explicit at the top of your module

Code works fine without it. So here are the declarations.

Option Explicit
Sub IJKL()
Dim nodupes As New Collection
Dim rng As Range, cell As Range
Dim itm As Variant, i As Long

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
i = i + 1
Debug.Print i, TypeName(itm), itm
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

--
Regards,
Tom Ogilvy



"Papa Jonah" wrote in message
...
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

counting items in a range
 

never mind, I got it without complicating it with extra stuff.

Thank you very much!

"Tom Ogilvy" wrote:

Possibly you have Option Explicit at the top of your module

Code works fine without it. So here are the declarations.

Option Explicit
Sub IJKL()
Dim nodupes As New Collection
Dim rng As Range, cell As Range
Dim itm As Variant, i As Long

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
i = i + 1
Debug.Print i, TypeName(itm), itm
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

--
Regards,
Tom Ogilvy



"Papa Jonah" wrote in message
...
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