Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the number of unique values within a large range
I would be most grateful if someone could offer me some help.
I need to count the number of unique values within a column range. The range is between B2:B65536. Each value consists of a series of alpha and numerical characters and there are blank cells within the range. There will be around 30,000 plus entries. I have used various formulas, but due to the vast number of rows of data they crash. An example of one of the formulas I used is below: =SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&""))) I am desperate for an answer but am confused as to how to do this? Any help or assistance would be very much greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the number of unique values within a large range
Why are you posting in two forums? -- Pecoflyer ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24219 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the number of unique values within a large range
Download and install the free add-in Morefunc.xll from this site:
http://xcell05.free.fr/morefunc/english/index.htm It has a function called COUNTDIFF that will do what you want. -- Biff Microsoft Excel MVP "SiH23" wrote in message ... I would be most grateful if someone could offer me some help. I need to count the number of unique values within a column range. The range is between B2:B65536. Each value consists of a series of alpha and numerical characters and there are blank cells within the range. There will be around 30,000 plus entries. I have used various formulas, but due to the vast number of rows of data they crash. An example of one of the formulas I used is below: =SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&""))) I am desperate for an answer but am confused as to how to do this? Any help or assistance would be very much greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the number of unique values within a large range
Hi,
I don't think the formula you entered will work. If there are no blank cells in the range you can use =SUM(1/COUNTIF(B2:B65536,B2:B65536)) entered as an array; or without an array: =SUMPRODUCT(1/COUNTIF(B2:B65536,B2:B65536)) These function are very slow when going against 65,000 row. Here is one formula which takes blanks into account: =SUM(1/(IF(COUNTIF(B2:B65536,B2:B65536)0,COUNTIF(B2:B655 36,B2:B65536),1)))-(COUNTBLANK(B2:B65536)0) This is array entered. if you know there are blanks you can just replace (COUNTBLANK(B2:B65536)0) with 1. -- Thanks, Shane Devenshire "SiH23" wrote: I would be most grateful if someone could offer me some help. I need to count the number of unique values within a column range. The range is between B2:B65536. Each value consists of a series of alpha and numerical characters and there are blank cells within the range. There will be around 30,000 plus entries. I have used various formulas, but due to the vast number of rows of data they crash. An example of one of the formulas I used is below: =SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&""))) I am desperate for an answer but am confused as to how to do this? Any help or assistance would be very much greatly appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the number of unique values within a large range
These function are very slow when going against 65,000 row.
They cause my machine to crash. But then, my machine is fairly "gutless" by todays standards. The function in the add-in calculates 65535 rows in the blink of an eye. -- Biff Microsoft Excel MVP "ShaneDevenshire" wrote in message ... Hi, I don't think the formula you entered will work. If there are no blank cells in the range you can use =SUM(1/COUNTIF(B2:B65536,B2:B65536)) entered as an array; or without an array: =SUMPRODUCT(1/COUNTIF(B2:B65536,B2:B65536)) These function are very slow when going against 65,000 row. Here is one formula which takes blanks into account: =SUM(1/(IF(COUNTIF(B2:B65536,B2:B65536)0,COUNTIF(B2:B655 36,B2:B65536),1)))-(COUNTBLANK(B2:B65536)0) This is array entered. if you know there are blanks you can just replace (COUNTBLANK(B2:B65536)0) with 1. -- Thanks, Shane Devenshire "SiH23" wrote: I would be most grateful if someone could offer me some help. I need to count the number of unique values within a column range. The range is between B2:B65536. Each value consists of a series of alpha and numerical characters and there are blank cells within the range. There will be around 30,000 plus entries. I have used various formulas, but due to the vast number of rows of data they crash. An example of one of the formulas I used is below: =SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&""))) I am desperate for an answer but am confused as to how to do this? Any help or assistance would be very much greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating the number of unique values with a range | Excel Discussion (Misc queries) | |||
Counting the number of unique values within a range | Excel Discussion (Misc queries) | |||
count unique values in a filtered range | Excel Worksheet Functions | |||
Unique values from date range | Excel Discussion (Misc queries) | |||
How do I get the unique values from a range? | Excel Worksheet Functions |