counting repeats.

[wabbleknee]

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

 

[lhkittle]

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(D15,E1)

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

Regards,
Howard

 

[wabbleknee]

wrote in message

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(D15,E1)

Where E1 has the name you choose to lookup and count (Jones, Bill etc) and
D15is 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.

 

[Claus Busch]

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

 

[wabbleknee]

"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

 

[Claus Busch]

Hi again,

please look here for the workbook "Pivot":
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
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

 

[wabbleknee]

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=9378AAB6121822A3&id=9378AAB6121822A3!191
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

 

[lhkittle]

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

 

[wabbleknee]

Tx Howard!!

wrote in message

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