Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
RPC@Frito
 
Posts: n/a
Default how can I count distinct names in an excel list?

I'm using Excel 97 and have a list of names. The entire list is 22,000 rows
or so, and all the names in the list are repeated numerous times (it's
downloaded data). I don't want to delete all the duplicates but want to be
able to count how many distinct names there are in the list.

Any solutions out there?

Thx.
  #2   Report Post  
Paul B
 
Posts: n/a
Default

Here is one way,

=COUNT(IF(FREQUENCY(A:A,A:A),1))
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"RPC@Frito" wrote in message
...
I'm using Excel 97 and have a list of names. The entire list is 22,000
rows
or so, and all the names in the list are repeated numerous times (it's
downloaded data). I don't want to delete all the duplicates but want to
be
able to count how many distinct names there are in the list.

Any solutions out there?

Thx.



  #3   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

Easiest would be to use datafilteradvanced filter, copy to another
location and unique records only, then count the extracted list

=COUNTA(extracted_range)


or use a formula

=SUMPRODUCT((A1:A22000<"")/COUNTIF(A1:A22000,A1:A22000&""))


Regards,

Peo Sjoblom

"RPC@Frito" wrote in message
...
I'm using Excel 97 and have a list of names. The entire list is 22,000

rows
or so, and all the names in the list are repeated numerous times (it's
downloaded data). I don't want to delete all the duplicates but want to

be
able to count how many distinct names there are in the list.

Any solutions out there?

Thx.



  #4   Report Post  
Gord Dibben
 
Posts: n/a
Default

Paul

This is the second time you have posted this formula in two days.

Have you tried it on any column of data other than numerics?

Returns 0 for me and the Help states it won't work on blanks or text cells.

Frito

Suggest you look at Chip Pearson's site......

http://www.cpearson.com/excel/duplic...CountingUnique


Gord Dibben Excel MVP

On Wed, 2 Feb 2005 15:53:58 -0500, "Paul B"
wrote:

Here is one way,

=COUNT(IF(FREQUENCY(A:A,A:A),1))


  #5   Report Post  
Paul B
 
Posts: n/a
Default

Gord, sorry did not check it on text, but it looks like it does work over a
range with blank cells, on numerics.

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Paul

This is the second time you have posted this formula in two days.

Have you tried it on any column of data other than numerics?

Returns 0 for me and the Help states it won't work on blanks or text
cells.

Frito

Suggest you look at Chip Pearson's site......

http://www.cpearson.com/excel/duplic...CountingUnique


Gord Dibben Excel MVP

On Wed, 2 Feb 2005 15:53:58 -0500, "Paul B"

wrote:

Here is one way,

=COUNT(IF(FREQUENCY(A:A,A:A),1))






  #6   Report Post  
Gord Dibben
 
Posts: n/a
Default

Paul

Correct. Does work with blanks it the range. My bad.

Gord

On Thu, 3 Feb 2005 08:55:49 -0500, "Paul B"
wrote:

Gord, sorry did not check it on text, but it looks like it does work over a
range with blank cells, on numerics.

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Paul

This is the second time you have posted this formula in two days.

Have you tried it on any column of data other than numerics?

Returns 0 for me and the Help states it won't work on blanks or text
cells.

Frito

Suggest you look at Chip Pearson's site......

http://www.cpearson.com/excel/duplic...CountingUnique


Gord Dibben Excel MVP

On Wed, 2 Feb 2005 15:53:58 -0500, "Paul B"

wrote:

Here is one way,

=COUNT(IF(FREQUENCY(A:A,A:A),1))




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
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Count Unique Names in list w/ Additional Criteria? Nodak Excel Worksheet Functions 1 January 26th 05 12:15 AM
Excel 2003: Match one list against another and highlight differenc smithers2002 Excel Worksheet Functions 1 January 11th 05 04:22 PM
How do I put a list of names and e-mail addresses in excel so tha. trav Excel Discussion (Misc queries) 4 December 2nd 04 03:56 AM
How do I extract the last name in Excel, from a list of names lik. RobertC Excel Worksheet Functions 4 November 17th 04 07:52 AM


All times are GMT +1. The time now is 10:13 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"