Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default 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
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy and paste VBA repeats and repeats Marilyn Excel Discussion (Misc queries) 4 June 7th 09 03:49 PM
NO REPEATS HOMER Charts and Charting in Excel 3 July 11th 08 09:55 PM
counting repeats of data Brad K. Excel Programming 1 June 26th 07 05:41 AM
Count repeats Daniel Bonallack Excel Worksheet Functions 7 December 10th 04 06:13 PM
no repeats in a column repeat Excel Worksheet Functions 2 November 3rd 04 10:36 PM


All times are GMT +1. The time now is 08:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"