counting repeats.

  • Thread starter Thread starter wabbleknee
  • Start date Start date
W

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
 
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
Click to expand...

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
 
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
Click to expand...

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.
 
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.
Click to expand...

or use advanced filter without duplicates


Regards
Claus Busch
 
"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.
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top