counting repeats.
 

I have a column of peoples names. What I want to do is count the number of
visits, list the clients name and total of visits for a specified period. #
of clients ~500 month. Appreciate some guidance. Tx

i.e. (column e)
Jones, bill
jones, bill
smith, bob
Jones, bill
smith, ann

Desired results;

jones, bill 3
smith, bob 1
smith, ann 1

counting repeats.
 

On Thursday, March 21, 2013 5:41:39 PM UTC-7, wabbleknee wrote:
I have a column of peoples names. What I want to do is count the number of

visits, list the clients name and total of visits for a specified period. #

of clients ~500 month. Appreciate some guidance. Tx



i.e. (column e)

Jones, bill

jones, bill

smith, bob

Jones, bill

smith, ann



Desired results;



jones, bill 3

smith, bob 1

smith, ann 1

Try =E1&" "&COUNTIF(D1:D5,E1)

Where E1 has the name you choose to lookup and count (Jones, Bill etc) and D1:D5is the list of folks.

Regards,
Howard

counting repeats.
 

wrote in message
...

On Thursday, March 21, 2013 5:41:39 PM UTC-7, wabbleknee wrote:
I have a column of peoples names. What I want to do is count the number
of

visits, list the clients name and total of visits for a specified period.
#

of clients ~500 month. Appreciate some guidance. Tx



i.e. (column e)

Jones, bill

jones, bill

smith, bob

Jones, bill

smith, ann



Desired results;



jones, bill 3

smith, bob 1

smith, ann 1

Try =E1&" "&COUNTIF(D1:D5,E1)

Where E1 has the name you choose to lookup and count (Jones, Bill etc) and
D1:D5is the list of folks.

Regards,
Howard

I understand how I could look up each name, but that would take hours and
hours, I am talking 500+ per month. What I was looking for was to be able
to scan the name column and print out unique names, and the count of that
unique name(s), without entering the search name.

counting repeats.
 

Hi,

Am Sat, 23 Mar 2013 14:38:28 -0400 schrieb wabbleknee:

I understand how I could look up each name, but that would take hours and
hours, I am talking 500+ per month. What I was looking for was to be able
to scan the name column and print out unique names, and the count of that
unique name(s), without entering the search name.

insert a pivot table. Drag the names in rows and in values.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

counting repeats.
 

Hi,

Am Sat, 23 Mar 2013 19:41:54 +0100 schrieb Claus Busch:

insert a pivot table. Drag the names in rows and in values.

or use advanced filter without duplicates


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

counting repeats.
 

"Claus Busch" wrote in message ...

Hi,

Am Sat, 23 Mar 2013 14:38:28 -0400 schrieb wabbleknee:

I understand how I could look up each name, but that would take hours and
hours, I am talking 500+ per month. What I was looking for was to be
able
to scan the name column and print out unique names, and the count of that
unique name(s), without entering the search name.

insert a pivot table. Drag the names in rows and in values.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus, would be glad to do that, but need a little help on a pivot table.
I filled in some different names in a column , some are repeated. Ran the
pivot table, it does give me unique names, but not sure on how to get a
count for each name. Tx. Mike

counting repeats.
 

Hi again,

please look here for the workbook "Pivot":
https://skydrive.live.com/#cid=9378A...121822A3%21191
The source for the pivot table is a dynamic range. You can enter
additional names and right click on pivot table to refresh the table.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

counting repeats.
 

Got it, thank you very much. Mike

"Claus Busch" wrote in message ...

Hi again,

please look here for the workbook "Pivot":
https://skydrive.live.com/#cid=9378A...121822A3%21191
The source for the pivot table is a dynamic range. You can enter
additional names and right click on pivot table to refresh the table.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

counting repeats.
 

On Thursday, March 21, 2013 5:41:39 PM UTC-7, wabbleknee wrote:
I have a column of peoples names. What I want to do is count the number of

visits, list the clients name and total of visits for a specified period. #

of clients ~500 month. Appreciate some guidance. Tx



i.e. (column e)

Jones, bill

jones, bill

smith, bob

Jones, bill

smith, ann



Desired results;



jones, bill 3

smith, bob 1

smith, ann 1

For a vb solution you could try this:
Where the list of name you want to count are in C1 and down.
Where the 'many names in a list I want to search' is in A1 and down.
See results in column M.

With three names to search for in C and 18,000+ like names in A, less than a second to process.

Sub CountNameList()

Dim LUp As Range
Dim NmeRng As Range
Dim c As Range
Dim Ct As Long

Set LUp = Range("C1:C" & Range("C" & Rows.count).End(xlUp).Row)
Set NmeRng = Range("A1:A" & Range("A" & Rows.count).End(xlUp).Row)
For Each c In LUp
Ct = WorksheetFunction.CountIf(NmeRng, c.Value)
Range("M2000").End(xlUp).Offset(1, 0) = c.Value & " " & Ct
Ct = 0
Next c
End Sub

Regards,
Howard

counting repeats.
 

Tx Howard!!

wrote in message
...

On Thursday, March 21, 2013 5:41:39 PM UTC-7, wabbleknee wrote:
I have a column of peoples names. What I want to do is count the number
of

visits, list the clients name and total of visits for a specified period.
#

of clients ~500 month. Appreciate some guidance. Tx



i.e. (column e)

Jones, bill

jones, bill

smith, bob

Jones, bill

smith, ann



Desired results;



jones, bill 3

smith, bob 1

smith, ann 1

For a vb solution you could try this:
Where the list of name you want to count are in C1 and down.
Where the 'many names in a list I want to search' is in A1 and down.
See results in column M.

With three names to search for in C and 18,000+ like names in A, less than a
second to process.

Sub CountNameList()

Dim LUp As Range
Dim NmeRng As Range
Dim c As Range
Dim Ct As Long

Set LUp = Range("C1:C" & Range("C" & Rows.count).End(xlUp).Row)
Set NmeRng = Range("A1:A" & Range("A" & Rows.count).End(xlUp).Row)
For Each c In LUp
Ct = WorksheetFunction.CountIf(NmeRng, c.Value)
Range("M2000").End(xlUp).Offset(1, 0) = c.Value & " " & Ct
Ct = 0
Next c
End Sub

Regards,
Howard