counting items in a range

[Guest]

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

 

[Bob Phillips]

=countif(A:A,1)

etc

--

HTH

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.

 

[Guest]

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]

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

 

[Dave Peterson]

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

 

[Guest]

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.

 

[Guest]

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

Thank you very much!