Sorting, FIltering & Counting
 

Hi all,

New to excel and have no idea of how to proceed, any help or pointers
would be greatly appreciated.

I have a column of Names, with multiple instances of each name, and I
need to find out how many entries there are for each name. This column
will be added to regularly and the number of instances for each name
will probably change over time

How should I approach this?


--
paragtim
------------------------------------------------------------------------
paragtim's Profile: http://www.excelforum.com/member.php...o&userid=34010
View this thread: http://www.excelforum.com/showthread...hreadid=537740

Sorting, FIltering & Counting
 

Sub Getcount()
Dim nodupes As Collection
Set nodupes = New Collection
Set rng = Range("A1", Range("A1").End(xlDown))
Range("C:D").ClearContents
On Error Resume Next
For Each cell In rng
nodupes.Add cell.Value, cell.Text
Next
For i = 1 To nodupes.Count
Cells(i, "C").Value = nodupes.Item(i)
Cells(i, "D").Formula = "=countif(A:A,C" & _
i & ")"
Next i

End Sub

--
Regards,
Tom Ogilvy


"paragtim" wrote:


Hi all,

New to excel and have no idea of how to proceed, any help or pointers
would be greatly appreciated.

I have a column of Names, with multiple instances of each name, and I
need to find out how many entries there are for each name. This column
will be added to regularly and the number of instances for each name
will probably change over time

How should I approach this?


--
paragtim
------------------------------------------------------------------------
paragtim's Profile: http://www.excelforum.com/member.php...o&userid=34010
View this thread: http://www.excelforum.com/showthread...hreadid=537740

Sorting, FIltering & Counting
 

I have been trying to work this out since the thread was raised. I'm
pretty sure the problem is between the keyboard and the seat.

The background:
Sheet1 has data in columns a - i. Columns A to c has time and date
info. Column d has the name of the agent raising the query.
Columns e - i has the outcomes from the task undertaken.
Sheet 1 has auto filters set on row 26 columns a - i
New data is added by inserting rows from row 29

What I want to do is generate a unique list of names on sheet 2 in
Column C with, along side each name, in Column D, the number of times
the agents name has appeared in Column D on sheet 1. For example Smith
6, Evans 3

Tom Olglvy!! sent me the code below but it generates an error

"Run Time Error 1004" Application defined or object-defined error

Can anyone help me put it right please

Many thanks


Sub Getcount()
Dim nodupes As Collection
Set nodupes = New Collection
Set rng = Range("sheet1!D27", Range("Sheet!d27").End(xlDown))
Range("sheet2!C:D").ClearContents
On Error Resume Next
For Each cell In rng
nodupes.Add cell.Value, cell.Text
Next
For i = 1 To nodupes.Count
Cells(i, "C").Value = nodupes.Item(i)
Cells(i, "D").Formula = "=countif(A:A,C" & i & ")"
Next i

End Sub


--
paragtim
------------------------------------------------------------------------
paragtim's Profile: http://www.excelforum.com/member.php...o&userid=34010
View this thread: http://www.excelforum.com/showthread...hreadid=537740

Sorting, FIltering & Counting
 

How about trying this:

Option Explicit
Sub Getcount()
Dim nodupes As Collection
Set nodupes = New Collection
Dim rng As Range
Dim cell As Range
Dim i As Long
With Worksheets("sheet1")
Set rng = .Range("d27", .Range("d27").End(xlDown))
End With
With Worksheets("sheet2")
.Range("C:D").ClearContents
On Error Resume Next
For Each cell In rng
nodupes.Add cell.Value, cell.Text
Next
For i = 1 To nodupes.Count
.Cells(i, "C").Value = nodupes.Item(i)
.Cells(i, "D").Formula _
= "=countif(" & rng.Address(external:=True) & ",C" & i & ")"
Next i
End With

End Sub




paragtim wrote:

I have been trying to work this out since the thread was raised. I'm
pretty sure the problem is between the keyboard and the seat.

The background:
Sheet1 has data in columns a - i. Columns A to c has time and date
info. Column d has the name of the agent raising the query.
Columns e - i has the outcomes from the task undertaken.
Sheet 1 has auto filters set on row 26 columns a - i
New data is added by inserting rows from row 29

What I want to do is generate a unique list of names on sheet 2 in
Column C with, along side each name, in Column D, the number of times
the agents name has appeared in Column D on sheet 1. For example Smith
6, Evans 3

Tom Olglvy!! sent me the code below but it generates an error

"Run Time Error 1004" Application defined or object-defined error

Can anyone help me put it right please

Many thanks

Sub Getcount()
Dim nodupes As Collection
Set nodupes = New Collection
Set rng = Range("sheet1!D27", Range("Sheet!d27").End(xlDown))
Range("sheet2!C:D").ClearContents
On Error Resume Next
For Each cell In rng
nodupes.Add cell.Value, cell.Text
Next
For i = 1 To nodupes.Count
Cells(i, "C").Value = nodupes.Item(i)
Cells(i, "D").Formula = "=countif(A:A,C" & i & ")"
Next i

End Sub

--
paragtim
------------------------------------------------------------------------
paragtim's Profile: http://www.excelforum.com/member.php...o&userid=34010
View this thread: http://www.excelforum.com/showthread...hreadid=537740

--

Dave Peterson