Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Count Unique Names in list w/ Additional Criteria? | Excel Worksheet Functions | |||
Excel 2003: Match one list against another and highlight differenc | Excel Worksheet Functions | |||
How do I put a list of names and e-mail addresses in excel so tha. | Excel Discussion (Misc queries) | |||
How do I extract the last name in Excel, from a list of names lik. | Excel Worksheet Functions |